Creating a Date Column using MQuery Without External Inputs (Power BI): A Step-by-Step Guide

Mquery can sometime seem alien to us and we prefer creating Mquery with the drag and drop features within Power BI. However after reading this you will soon realize that Mquery have much more to offer if you learn how to write it. Also my aim here is to make this concept as beginner friendly as possible, so that you can actually go ahead and use these steps in your projects whenever required. So lets jump into it!

What is Mquery?

In a nutshell, Mquery is a programming language used by Power Query engine for data transformation. It has a versatile toolkit that lets you manipulate, clean, and refine your data. With MQuery, you can perform a range of tasks, like filtering, merging different data sources, reformatting data or in this case create data out of thin air.

What will you learn?

We will focus on writing Mquery in formula bar and creating a Date column using Mquery which can be static or dynamic and contains dates ranges of our choice. We will not use any data input for this and will be done completely with just Mquery formula.

Requirement

1. Create a DATE table which contains 1 column of dates. Ranging from 1st Dec 2023 to 10th Dec 2023
2. Create another DATE table which contains 1 column of dates, starting from 1st Jan 2022. However this column needs to be dynamic, which means at every day this table needs to add an additional row for the current date.

Solution #1 – Static Date Column using Mquery

Lets begin creating our first table which will have a static list of dates ranging from 1/12/2023 till 10/12/2023. So fire up your Power BI Desktop.

• From the main Power BI window, click on Get Data button and then click on Blank Query

We are using a blank query because we dont have a external data input, and we will solely create this table using only Mquery.

You will now see the power query editor, and a new query created at the left panel. We will now start writing the Mquery in the formula bar at the middle

• Now the first thing we need is to have a starting date, which we will use as a reference to calculate remaining entries. In this case our starting date is “1/1/2023”. So to define a date in Mquery, you need to enter the below function in the formula bar:

=#date(2023, 12, 1)

When we define a date in Mquery, the syntax starts with #date

Now you must notice that a date value has been created and the step is automatically named as “Source” in the applied steps section

• Now click on the “fx” button in the formula bar section and enter below formula

• You will see “=Source”, remove that and instead enter below formula

= List.Dates(Source, 10, #duration(1,0,0,0))

Formula Explanation:

Now don’t get worried with above formula, I will explain it you in simple terms. Basically List.Dates create a list of dates with few parameters. i.e. Start Date, Count of Rows & Duration Interval.

1. So obviously for Start Date we using Source, which we set earlier i.e. 1/12/2023.
2. For Count of Rows, since we need dates till 10/12/2023, we gave it 10 for adding 10 rows
3. Lastly the duration which starts with #duration, this parameter helps us determine the duration between the current row & the next row. Since we want each row to be incremented by 1 day, we simply gave it (1,0,0,0). Which mean 1 day, 0 hours, 0 minutes & 0 seconds.

As of now this has just created a list, but the requirement said to create a table. So to create a table, follow below steps:

• Go to List Tools >> Transform
• Click on “To Table”

• Since we are dealing with date values, you can leave the next prompt as default and press OK.

So finally click on “Close & Apply” in the home tab and you have successfully created a new static table with Dates from 1/1/2023 to 10/12/2023.

Great, now lets move on to the next requirement!

Solution #2 – Dynamic Date Column using Mquery

For this, we will again create a blank query i.e. Get Data >> Blank Query and then create the begining date using #date. But remember this time the start date is 1/1/2022. So enter the below function in the formula bar:

= #date(2022,1,1)

Now again click on the “Fx” button besides the formula bar and enter a new steps. And now enter the below formula in the formula bar:

= List.Dates(Source, Number.From(DateTime.LocalNow()) - Number.From(Source), #duration(1,0,0,0))

Formula Explanation:

Similar to before List.Dates is creating a list of dates, and then the first parameter is Source like earlier. However if you remember this requirement was to make this dynamic, so in the row count parameter we need some different magic!

In the middle parameter of count, we are simply first getting today’s date using “DateTime.LocalNow(), and then converting that to a number, because this parameter only accepts integer values. Then we are getting the difference between integer values of Source date and Today’s date.

Finally the #duration parameter is exactly same as earlier and is incremeting each row by 1 day.

Finally you should have a list of dates which now needs to be converted to a table to complete our requirement. So similar to earlier, follow below steps:

• Go to List Tools >> Transform
• Click on “To Table”
• Then in the next prompt, keep everything as default and press ok

That’s it, congratulations, you have successfully created a date column using Mquery. Hope you learned something new today. See you in the next one!