How To Calculate Age From Date of Birth in Power Query

Mitchell Pearson, a trainer at Pragmatic Works, dives into a practical Power Query tutorial focused on working with date data. In this video, he explains how to handle date conversion errors and demonstrates how to calculate age from a date of birth directly in Power Query, emphasizing best practices for efficient data modeling.

Handling Date Conversion Errors in Power Query

Mitchell begins the tutorial by addressing a common issue: converting text-based date fields into proper date formats. He demonstrates a typical error where attempting to convert a text column directly to a date fails. Key steps to resolve this include:

1. Change the column’s data type to Date/Time first.
2. Then, convert the column to a Date data type.
3. Select Add New Step rather than Replace Current to ensure proper data transformation.

This two-step approach prevents parsing errors commonly encountered when working with CSV or Excel data imports in Power Query.

Why Convert Dates in Power Query Instead of DAX?

Mitchell emphasizes that date transformations should be performed in Power Query rather than DAX (Data Analysis Expressions). Benefits of handling date conversions in Power Query include:

• Optimized Data Storage: Calculations performed before data compression reduce model size.
• Better Performance: Pre-aggregated data leads to faster queries and reporting.
• Simpler Data Modeling: Performing transformations at the source simplifies DAX measures later.

Calculating Age from Date of Birth in Power Query

Next, Mitchell explains how to calculate a customer’s age using Power Query’s UI, without writing any code:

1. Select the Birthdate column.
2. Go to the Add Column tab and select Date.
3. Choose Age. This generates a new column with the age calculated in days.
4. Change the column to Total Years using the Duration menu.

The result is an age calculation based on the difference between the current date and the date of birth.

Refining the Age Calculation

After generating the age column, Mitchell demonstrates how to refine the calculation to avoid decimal results:

• Select the Age column.
• Under the Transform tab, choose Rounding and select Round Down.
• This ensures that age is shown as a whole number, preventing premature rounding up.

Mitchell also highlights how Power Query simplifies this process with an M code expression that divides the age in days by 365. He mentions the ability to adjust this calculation slightly to account for leap years by changing the divisor to 365.25.

Additional Numeric Transformations in Power Query

Mitchell further explores additional numeric transformations available in Power Query, including:

• Absolute Values: Converts negative numbers to positive.
• Power and Square Root: Apply basic mathematical operations.
• Modulo (Remainder): Returns the remainder after division.
• Percentage Of: Calculates a static percentage of another column’s value.

These transformations offer a range of capabilities for preparing data before building reports.

Key Takeaways

Mitchell concludes by emphasizing the importance of performing date conversions and age calculations within Power Query, rather than DAX, to optimize data models. Key points covered in the video include:

• Always convert date columns using a two-step approach (Date/Time → Date).
• Use the Age transformation in Power Query for quick calculations.
• Round down age values to avoid premature rounding up.
• Leverage Power Query’s numeric tools for additional data transformation tasks.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.