Results 1 to 6 of 6

Thread: Power Query: Age from Birthdate on Action date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query: Age from Birthdate on Action date

    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
    Last edited by SOQLee; Jan 26th, 2017 at 12:46 PM.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query: Age from Birthdate on Action date

    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)

  3. #3
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,811
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query: Age from Birthdate on Action date

    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 by MarcelBeug; Jan 26th, 2017 at 03:16 PM.
    Specializing in Power Query (M).
    Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
    Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query: Age from Birthdate on Action date

    Got it to work! Much appreciated. Thank you.

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query: Age from Birthdate on Action date

    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)

  6. #6
    Board Regular
    Join Date
    Feb 2014
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query: Age from Birthdate on Action date

    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 by billszysz; Jul 14th, 2017 at 06:14 PM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •