Quote of the Day
...in any bureaucratic organization there will be two kinds of people: those who work to further the actual goals of the organization, and those who work for the organization itself. Examples in education would be teachers who work and sacrifice to teach children, vs. union representatives who work to protect any teacher including the most incompetent. The Iron Law states that in all cases, the second type of person will always gain control of the organization, and will always write the rules under which the organization functions.
— Pournelle's Iron Law of Bureaucracy.
Figure 1: Jimmy Carter, 39th President of the United States.
I was listening to news the other night when I heard a reporter mention that Jimmy Carter just became the longest-lived US president. I thought verifying this fact would be a good Power Query exercise. He had just surpassed George H.W. Bush, the previous record holder.
My approach was to obtain the birth and death information (if present) by scraping the Wikipedia pages for US presidents. For the dead presidents, I then computed their age at death. For the living presidents, I computed their age as of 25-March-2019.
Doing historical work in Excel is always an adventure because it does not handle dates prior to 1900. Power Query does not have this issue. On the downside, Power Query does not support a function for computing date differences in terms of years, months, and days (Excel has DATEDIF). I found an M-code routine that partially solved the issue, which I modified to fully solve the issue. My Excel worksheet is available here.
My routine for computing the ages of the presidents in years, months, and days is shown in the following code block. This is a modified version of some work by Imke Feldmann, a goddess of Power Query. The routine adds a column with a record in each president's row that can be expanded to show years, months, and days.
AC = Table.AddColumn(CT, "RecordColumn", each
[
BirthdayFactor = if Date.DayOfYear([Born])<=Date.DayOfYear([Died]) then 0 else 1,
DayFactor= if Date.Day([Died])<Date.Day([Born]) then -1 else 0,
Years = Date.Year([Died])- Date.Year([Born])-BirthdayFactor,
Months = Date.Month([Died])-Date.Month([Born]) + 12*BirthdayFactor + DayFactor,
Days = Number.From([Died])-Number.From(#date(if (Date.Month([Died]) + DayFactor)=0 then Date.Year([Died])-1 else Date.Year([Died]), if (Date.Month([Died]) + DayFactor)=0 then 12 else Date.Month([Died]) + DayFactor, Date.Day([Born])))
] ),
Table 1 shows my table of the longest-lived dead presidents. Note that my algorithm does not count their last day – just like DATEDIF. I compared my result with the output from this website and they agree.
Table 1: Longest Lived Dead Presidents.
Table 2 shows my table for the ages of the living US presidents. As you can see, Jimmy Carter is now the longest living US president.
Table 2: Ages of the Living US Presidents.