Power Query: Age from Birthdate on Action date

[SOQLee]

In Power Query, is it possible to determine the AGE from the BIRTH DATE at a specific ACTION date? I have the the following columns:

Birthdate	ActionDate
1980-01-01	2017-01-26

<tbody>
</tbody>

 

[SOQLee]

Further to my question. In PowerPivot I use the following DAX formula to create a column. I'm wondering if it can be done in PowerQuery.

=DIVIDE([ActionDate]-[Birthdate],365)

 

[MarcelBeug]

The correct way to calculate the age is to take the difference between the year parts and subtract 1 if the ActionDate is prior to the birthday in that year.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Birthdate", type date}, {"ActionDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Age", each Date.Year([ActionDate]) - Date.Year([Birthdate]) - 
                            (if Date.Month([ActionDate]) < Date.Month([Birthdate]) then 1 else 
                             if Date.Month([ActionDate]) = Date.Month([Birthdate]) and Date.Day([ActionDate]) < Date.Day([Birthdate]) then 1 else 0))
in
    #"Added Custom"

Remark: if you choose "From table" when creating the query in Excel, then the data types are by default changed to datetime; I adjusted this to date.

 

[SOQLee]

Got it to work! Much appreciated. Thank you.

 

[SOQLee]

Is it possible to adjust this custom column formula below to prevent error value on refresh when there is no value to calculate from in the [Birthdate] column?

=Date.Year([Appl Dt]) - Date.Year([Birthdate]) -
(if Date.Month([Appl Dt]) < Date.Month([Birthdate]) then 1 else
if Date.Month([Appl Dt]) = Date.Month([Birthdate]) and Date.Day([Appl Dt]) < Date.Day([Birthdate]) then 1 else 0)

 

[billszysz]

#"Added Custom" = Table.AddColumn(#"Changed Type", "Age", each 
                                         if [Birthdate] = null 
                                         then 
                                              null 
                                          else 
                                              Date.Year([Appl Dt]) - Date.Year([Birthdate]) - Number.From(Date.ToText([Birthdate], "MMdd") > Date.ToText([Appl Dt], "MMdd")))