In today’s blog, I will briefly introduce the Extended Date Table 2.0. I first shared the code for this M function in April 2020. Since then, there have been a number of requests and updates on this Power Query date table. You can watch the full video of this tutorial at the bottom of this blog.
The most recent release of version 2 has resolved some inconsistencies in field names and value formats, but also includes some new functionality.
Accessing Power Query Extended Date Table 2.0
Option 1: eDNA Forum
So where can you go to get the code for this specific date table? Well, the first place is the Enterprise DNA Forum in the M Code showcase category.
This forum is accessible to everyone, to both members and non-members. If you happen to be on the forum and you see my badge, you can also click on that and you’ll find the extended date table in the featured topic. The hyperlink will go to this topic as well.
To get the code from the forum, simply copy the code you see at the bottom of the post. Make sure to copy everything from start to end.
Then, go back to Power BI and open the Power Query editor.
Then, add a new blank query.
For this tutorial, I’ve already done this step, so I’ll just select my blank query and open the advanced editor.
Select everything you see in the Advanced Editor window and paste the code you copied from the forum. Then press Done.
This returns a Power Query M function that you can invoke.
Option 2: Analyst Hub
You can also go to the Analyst Hub to get the code for this date table. But you must have a membership to access this specific platform.
Once in the Analyst Hub, just search for PQ: Extended, and you will find the code for the Extended Table in the PQ: Extended Date Table (Power Query M function) result. You can also see my username.
Next, you can copy the code directly from the result by hovering over it and clicking Copy.
Alternatively, you can view the document first by clicking Open Document then copy the code from there.
Let’s go back to Power BI and open Advanced Editor. Again, I’ve already added a new blank query, so I’m just going to select that and click Advanced Editor.
In the Advanced Editor window, select everything you see then paste in the code. Press Done.
Instead of a function, this method returns a record that includes the most recent version fxCalendar as well as the fxCalendarDepricated V1, and a ChangeLog.
If you want to use the new function, just click on that or drill down into it by right-clicking the white space beside it and adding it as a new query.
This will retain the record.
So this is one way, and the other way is just by clicking on that function.
This will drill down into our record.
It will also add the navigation step as shown below.
Simply click X to return the record.
Understanding Power Query Date Table Parameters
Power Query Date Table: Required Parameters
Let’s also take a look at the meaning and function of each of the date table parameters. First, the StartDate is a required parameter, and will be the first date in your date table.
The second and final required parameter of this function is the EndDate that signifies the last date in your date table.
Your date table should always span full years and cover all dates from the earliest to the latest date present in your data set.
Power Query Date Table: Optional Parameters
The optional FYStartMonthNum allows you to set a fiscal year start month. This will add fiscal years, quarters, periods, and weeks to the date table.
Note that fiscal weeks will always start on a Monday and can contain less than seven days in the first or last week of a fiscal year. However, if you emit this parameter, none of the fiscal columns will be added to your date table.
Holidays is an optional parameter to pass a list with holiday dates to this date table. It enables a boolean filter called IsHoliday, but will also impact fields IsBusinessDay, day type, and relative network days.
WDStartNum allows you to change the default weekday numbering from zero-based, meaning from zero to six into running from one to seven.
AddRelativeNetWorkdays allows you to include a Relative Network Day column to the date table.
***** Related Links *****
Creating A Date Table In Power BI The Fastest Way Possible
Extended Date Table Power Query M Function
The Importance Of Power BI Date Tables
Conclusion
In conclusion, the Extended Date Table 2.0 is a powerful tool for working with dates in Power Query.
It provides a comprehensive set of functions for generating a wide range of date-related information, making it an invaluable resource when working with large datasets with date information.
All the best,
Melissa de Korte
