Power BI Power Query

Correct age calculation in Power Query

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.

correct age calculation function in Power Query

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.

incorect age calculation result in Power Query

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.




1 comment on “Correct age calculation in Power Query

  1. Hello,

    I used your code to calculate age in Power Query, but I still got the wrong answer.
    I changed the code a little bit and then it worked. The problem is that you need extra parenthesis. Also if you convert to integer directly, it does not work when someones birthday is near. For example I calculated the age at 21-10-2021 for someone whose birthday is 22-10-2016. You formula gave me 5, but this person turns 5 tomorrow.
    I changed the code and this worked for me. Then afterwards you can round down in Power Query.

    try
    Double.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

Leave a Reply

Your email address will not be published. Required fields are marked *