Calculate Age from Date of Birth in Power Query M

Calculates a person's current age in whole years from a date of birth column.

Date & TimeIntermediateagedate of birthcalculatedate

Power Query M code

let
    Source = YourTable,
    AddedAge = Table.AddColumn(Source, "Age", each
        let
            Today = DateTime.Date(DateTime.LocalNow()),
            RawAge = Date.Year(Today) - Date.Year([DateOfBirth]),
            HasHadBirthday = (Date.Month(Today) > Date.Month([DateOfBirth])) or (Date.Month(Today) = Date.Month([DateOfBirth]) and Date.Day(Today) >= Date.Day([DateOfBirth]))
        in
            if HasHadBirthday then RawAge else RawAge - 1,
        Int64.Type)
in
    AddedAge

Generate a date table in Power Query M

Notes

Age is calculated relative to the refresh date, so it will change on each refresh as time passes.

Requirements

A table with a Date of Birth column.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Calculate Age from Date of Birth.