Correct age calculation in Power Query

It is not so easy to do age calculation in Power Query in a way that works all the time correctly. There are useful transformations that calculate age, but in specific situations, it is not working properly.

In my case, I have a table of two dates. One is a date of birth, but the other is a date for the age calculation.

Correct age calculation in Power Query

As a result, I adapted this mathematical trick that does the age calculation and gives the correct result.

try
  Int64.From(
    (
        Date.Year([Other Date])
        * 10000 + Date.Month([Other Date])
        * 100 + Date.Day([Other Date]) 
        - 
        Date.Year([Birth Date])
        * 10000 + Date.Month([Birth Date])
        * 100 + Date.Day([Birth Date])
    )
      / 10000
  )
otherwise
  0

Here is an M code for a full example.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyJDS3NjJR0lYyDLSM/IwNBCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Birth Date" = _t, #"Other Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Birth Date", type date}, {"Other Date", type date}}),
    #"Age Calculation" = Table.AddColumn(#"Changed Type", "Age", each try
  Int64.From(
    (
        Date.Year([Other Date])
        * 10000 + Date.Month([Other Date])
        * 100 + Date.Day([Other Date]) 
        - 
        Date.Year([Birth Date])
        * 10000 + Date.Month([Birth Date])
        * 100 + Date.Day([Birth Date])
    )
      / 10000
  )
otherwise
  0)
in
    #"Age Calculation"

There might be possible to adopt a much simpler age calculation using R like in this example with the conversion of accented characters to unaccented.

The problem with age calculation in Power Query

At first, I did a calculation with Power Query data transformation tools, but the result is not correct. Age must be 45 instead of 46. This problem might not be so often, but when it happens it might be dissapointing.

Here is an M code for that.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyJDS3NjJR0lYyDLSM/IwNBCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Birth Date" = _t, #"Other Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Birth Date", type date}, {"Other Date", type date}}),
    #"Inserted Age" = Table.AddColumn(#"Changed Type", "Age", each [Other Date] - [Birth Date], type duration),
    #"Inserted Total Years" = Table.AddColumn(#"Inserted Age", "Total Years", each Duration.TotalDays([Age]) / 365, type number),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Total Years",{{"Total Years", Int64.Type}})
in
    #"Changed Type1"

Other Power Query posts

How to calculate ISO year in Power Query.

How to calculate ISO week number in Power Query.

How to add leading zeros in R, Power Query, DAX or Excel.