Introducing Power Query Extended Date Table V2

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


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

Enterprise DNA Power BI On-Demand

The Latest

If you’ve been working with Python for data analysis or machine learning, you’ve likely come across NumPy arrays. They’re a powerful tool for handling numerical data, but sometimes, the data…

How to normalize a numpy array a quick guide.

Multiplying lists in Python is a common operation when performing mathematical computations or solving problems in data manipulation. There are multiple ways to achieve this task, depending on your requirements…

How to Multiply Lists in Python: A Simplified Guide

If you’ve been wrestling with Python lists and wondering how you can save them as a neat CSV file, you’re in the right place. One of the most common tasks…

How to Write a List to CSV in Python

Do you need to write an essay on the fall of the roman empire with accurate citations but have no time to find them? Are you tired of painfully having…

What is Caktus AI: A comprehensive overview

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality that pandas can provide you is the ability to modify the structure of a dataset….

Pandas Drop Index: Efficiently Remove DataFrame Rows or Columns

Working with strings is a common task in Python. You often need to figure out whether a string contains another string. The simplest methods to check if a Python string…

7 Ways to Check if a Python String Contains Another

Ever found yourself knee-deep in Python files, struggling to import a module from a parent directory? If so, you’re in good company. Python’s import system can sometimes feel like a…

Python Import from Parent Directory: A Quick Guide

Data is the backbone of businesses these days, and having proof that you know how to handle and make the most out of data is a big deal in the…

Microsoft DP-500

In programming, you may encounter situations where a variable does not have a value. These instances are commonly represented by a special value known as null. Many programming languages, such…

Null Python: 7 Use Cases With Code Examples

Truncating a floating-point number in Python is a common operation that many programmers encounter in various applications. The process involves removing the decimal place of a float, leaving only the…

Python Truncate Flow: A Streamlined Approach for Efficient Code Execution

OpenAI’s ChatGPT tool has taken the world by storm and has been at the forefront of revolutionizing the way we generate content, do research, and even create code. And just…

Is ChatGPT worth it, let's work it out.

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and easy to maintain. The Python programming language comes with a…

Python Inline If: Simplify Your Conditional Expressions

Load More