Power Query: Age from Birthdate on Action date

SOQLee

Board Regular
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:

BirthdateActionDate
1980-01-012017-01-26

<tbody>
</tbody>
 
Last edited:

SOQLee

Board Regular
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

Well-known Member
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.

Code:
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.
 
Last edited:

SOQLee

Board Regular
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

Active Member
Code:
#"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")))
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top