Future-Proof Your Career, Master Data Skills + AI

Blog

Future-Proof Your Career, Master Data Skills + AI

Introducing Power Query Extended Date Table V2

by | Power BI, Power Query, Power Query in Power BI

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. 

Introducing Power Query Extended Date Table V2

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.

Accessing Power Query Extended Date Table 2.0

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.

Accessing Power Query Extended Date Table 2.0

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.

Accessing Power Query Extended Date Table 2.0

Then, go back to Power BI and open the Power Query editor.

Accessing Power Query Extended Date Table 2.0

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.

Accessing Power Query Extended Date Table 2.0

Select everything you see in the Advanced Editor window and paste the code you copied from the forum. Then press Done.

Accessing Power Query Extended Date Table 2.0

This returns a Power Query M function that you can invoke. 

Accessing Power Query Extended Date Table 2.0

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.

Option 2: Analyst Hub

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. 

Option 2: Analyst Hub

Next, you can copy the code directly from the result by hovering over it and clicking Copy.

Option 2: Analyst Hub

Alternatively, you can view the document first by clicking Open Document then copy the code from there.

Option 2: Analyst Hub

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

Option 2: Analyst Hub

In the Advanced Editor window, select everything you see then paste in the code. Press Done.

Option 2: Analyst Hub

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. 

Option 2: Analyst Hub

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.  

Option 2: Analyst Hub

This will retain the record. 

Option 2: Analyst Hub

So this is one way, and the other way is just by clicking on that function.

Option 2: Analyst Hub

This will drill down into our record.

Option 2: Analyst Hub

It will also add the navigation step as shown below.

Option 2: Analyst Hub

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

Related Posts