Prepping the date dimension via the Query Editor

In some BI environments, it’s not to alter the source date table per the recipe or even modify the SQL view used to load the date dimension table; at least, not in the short term. In these situations, Power BI Desktop's Query Editor and M expressions can serve as an effective alternative to deliver the same columns necessary to drive robust date intelligence analysis.

In this recipe, an example date dimension M query is shared, which builds common date attributes as well as dynamic logical columns. Additionally, a process for adding sequential date intelligence columns via M expressions is also included.

How to do it...

Date dimension M Query

1. Create an M query that an existing SQL date table view, retrieves the last three years of dates, and computes 11 additional columns via M functions:
   • A filter is applied via Table.SelectRows() to only retrieve the last three years of dates, and conditional logic is used to populate dynamic Year Status and...