# Power Query:  Age from Birthdate on Action date



## SOQLee (Jan 26, 2017)

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-012017-01-26

<tbody>

</tbody>


----------



## SOQLee (Jan 26, 2017)

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 (Jan 26, 2017)

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 (Jan 27, 2017)

Got it to work!  Much appreciated.  Thank you.


----------



## SOQLee (Jul 14, 2017)

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 (Jul 14, 2017)

```
#"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")))
```


----------

