Using The M Function To Create An Extended Power BI Date Table

No comments

This tutorial will cover about the most important Power BI dimension table in any model, the Date Table.

The best practice when designing a model is to check for date fields. If they are present, always include a date dimension table.

A Date Table includes all the attributes that you may want to slice, dice, and filter your data with to get valuable insights. With it, a date field anywhere inside your model won’t be a problem.

Get The Power Query M Code In The Forums

In the Enterprise DNA Forum, you can type Date Table in the search bar, or switch to Categories and look for M Code Showcase. But the quickest way to find it is to look for “@melissa”.

Next, choose any subject, then click the badge, and then click the Featured Topic Extended Date Table.

You will then find the M code.

Power BI date table

Copy this whole measure and transfer it to the Power Query.

Application In Power Query

For this step, make sure the formula bar is visible. If it isn’t, go to View tabs and enable it.

Right click the Parameter and Function folder, choose New Query, and then Blank Query.

Then click the Advanced Editor and paste the Power Query M code. After that, change the query name to fxCalendar.

You need to set up the parameters before you can invoke this function. Set it up with the following details:

Invoke the function and then rename it to Dates.

It will now show you this date table with 32 columns. If you’re familiar with the M Code, you’ll probably recognize some columns here. The other columns are additional attributes or logic from the code that you used.

Power BI date table

In the Date Table shown, you can see Fiscal Year, Quarter, and Period columns, as well as the filter columns, IsAfterToday, IsWorkingDay, and IsHoliday.

Since the 4th parameter wasn’t set up, IsHoliday bears an Unknown value.

Moreover, included in this Date table are the Offset columns which simplify the DAX code you need to do some analysis.

If you want to add attributes, use the code and amend it to what you want. Also, if you want to delete unnecessary columns, go to Choose Columns and then deselect what you don’t need.

Make A Dynamic End Date

When developing a report, the start date is easy to set unlike the end date. The end date is tricky because it depends on the data inside your model and how long your report remains active.

To make a dynamic end date, we need to dynamically update the year.

First, create a new blank query.

The M equivalent of the DAX or Excel function is “DateTime.FixedLocalNow”. This is what the function does:

Power BI date table

Next, in the formula bar, use the function to extract the year value.

As you reach the end of the year, your data may spill over the next year. So, it’s best to add an additional year like this:

Power BI date table

After that, rename the query “MaxYear”.

Go back to your Date Table, and sub in the MaxYear variable in the function to have the year 2021 in your calendar.

Power BI date table

Invoke And Get The Holiday List Column

If you check your holiday table, you can see that it runs from 2018 to 2020 even if the max year is 2021.

To alter this, go to Source Step, and then sub in the MaxYear variable in the measure.

Your holiday table will then have the year 2021.

Next, go back and set up the parameters again. But this time, include the Holiday parameter by clicking Choose Column.

Select Holidays and the column that contains the dates then click OK.

After setting everything and clicking Invoke, you will see the “Holidays[Date]” variable in the formula bar. Copy that variable and delete the Invoked Function.

Go back to the Date Table, then sub the variable in the Source Steps’ function.

Power BI date table

Once it finishes loading and invoking the function, you will see that the IsHoliday column that once had unknown values now has True or False values in each row.

Power BI date table

It shows True if the date is a holiday, and False if otherwise.

Conclusion

The Power Query M code is a useful function in completing your Date Table report.

It shows you what you want to see inside your table, and conveniently gives you the data for your model.

You just have to get the code, set it up inside Power Query, and amend it according to what you need in your table.

All the best,

Melissa

Membership Banne
Center of Excellence

***** Related Links *****
Power BI Query Parameters: Optimizing Tables
Adding Workday And Weekend Numbers Into Your Date Table: Time Intelligence Technique In Power BI
Build A Comprehensive Date Table In Power BI Really Fast

***** Related Course Modules *****
Ultimate Beginners Guide to Power BI
Time Intelligence Calculations
Power Query Series

***** Related Support Forum Posts *****
Dynamic Date Table (Update Date Table)
Updating The Date Table
Multiple Date Table
For more date table queries to review see here….

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.