Power Query: Ageing

Welcome to our Power Query blog. This week, I look at calculating age.

I have some data for my imaginary salespeople. This week, I intend to calculate their age.

I extract my data to Power Query using ‘From Table’ on the ‘Get & Transform’ section of the Data tab.

I accept the defaults.

If I select the DOB column, on the ‘Add Column’ tab, I have the option of choosing ‘Age’ on the ‘Date’ dropdown of the ‘From Date’ section.

This doesn’t look much like an age. This is because it gives me the duration in days, hours, minutes and seconds between the current date and DOB. That’s very accurate, but not quite what I am looking for. I choose to add another column, this time based on Age.

Using the Duration dropdown, I can create a new column for the total number of years in the Age column.

This is much better, but it’s still a little too accurate for my needs. I choose to round down the value (no adult likes to round up their age!).

I do this on the Transform tab, where I can choose to ‘Round Down’ from the Rounding dropdown in the ‘Number Column’ section.

Now I have the age of my salespeople in the format I require. I rename my column and remove the column I used to get to my result.

I can also use this method to calculate the age of a product or a duration in years between two dates.

Come back next time for more ways to use Power Query!