I’m going to show you how to add a parameter in the M function in your date table query. This example is based on a question that was raised on the Enterprise DNA Forum. You may watch the full video of this tutorial at the bottom of this blog.
The question was about how to switch the numbering of the 1st day of the week from 0 to 1. Note that this doesn’t change the actual start of the weekday. In this date table, the start of the week will always be Monday. It’s just about referring to Monday as Day 1 instead of Day 0.
This process isn’t necessarily complex, but you do need a basic understanding of how M language is structured. If you’re an Enterprise DNA member, you can check out the Advanced Data Transformations & Modeling course. The introduction touches on the topic.
The M Function And The Extended Date Table
Let me start off by going into the Power Query. I’ve already copied the date table M function here.
I got the M function from the M Code Showcase category in the Enterprise DNA Forum. The code is under the topic Extended Date Table.
If I open the Advanced Editor, this is the code.
There’s a lot of M code in there, which could be distracting.
Creating A Test Query
Since there’s too much going on within the M function, I’m going to start with a new blank query instead.
I’m going to name this Test Query.
Next, I’m going to open the Advanced Editor.
Because I’m going to create a function, I’m going to add a pair of parentheses and delete all the default code in there.
To start the function off, I’m going to declare an optional parameter.
Next, I’m going to add a variable. I’ll call this variable WDStartNum since it’s going to stand for the weekday start number. I also need to declare its type, so I’ll type in number.
I’m declaring the type to prevent errors. If I just leave it as any type, this means that it could pass a table or a date value instead of just passing numbers.
Next step is to add the let clause and the in clause.
Under the let clause, I need a variable name. So I’m going to call this variable WDStart.
Now, I need to test if the WDStartNum has been passed. If it passes, it won’t equate to null. So I’m going to declare that if the WDStartNum is not equal to null, I want a value to be returned.
As for the in clause, I want the same step to be passed. So I’ll just put WDStart there, which is what we named our variable earlier under the let clause.
Once I press Done, the function will be created. So I’ll try to pass a value through that function.
I’m going to try typing in the letter “a”.
Remember that earlier I declared that the value must be a number. Because it recognizes that the value I entered doesn’t meet this requirement, it won’t allow me to enter that parameter.
So I’m going to delete that and just leave the space blank.
Once I click on Invoke, it returns a value of 0.
Going back to my original query, it states that if the value is not equal to null, the WDStartNum should be returned. If it’s equal to null then a 0 should be returned.
So that 0 is correct.
This time, I’m going to try and pass a value by changing the number in the TestQuery. True enough, it returns a “1” once I hit enter.
Now, let me try to pass 2020 in the TestQuery. Once I hit enter, it also returns 2020.
Evidently, I don’t want this to happen. Since I want a value that represents the start of the week, I want either a 0 or a 1 showing up. This means that I need to create another test to see if the number entered is either a 0 or a 1. I can use the List.Contains function for this.
Using List.Contains
I created a blank query and entered List.Contains without using a parenthesis.
Once I press enter, I can see the documentation on that function.
List.Contains indicates whether the list contains a value.
So it requires a list as the first parameter, and then a value is added after that. If that value is found inside the list, it returns true. Otherwise, it will return false.
I’ll open the Advanced Editor again so that I can make the necessary changes on my query.
Instead of testing whether a value is not equal to null, I’m going to use List.Contains.
Now, I’ll give a list of values to be applied to this function.
I’ll use curly brackets as a list initializer. Inside those curly brackets, I’ll put 0, 1. I’ll add a comma after closing that bracket.
For the second parameter, I’m going to use WDStartNum. Then, I’ll add the closing parenthesis.
With this in place, it means that a 0 or 1 should give me the WDStartNumber. If anything other than a 0 or 1 is placed, I should get a result of 0.
Let me test that by putting 2020 in the TestQuery. As expected, this returns a “0” (zero).
That’s because the value 2020 is not in the list.
Adding The Parameter Into The M Function
Now that I’m sure the code is working, I can finally put it inside the actual date table and M function. I’ll open the Advanced Editor then I’m going to copy the logic.
Then I’ll step into the date table query.
I’ll open the original M function through the Advanced Editor.
Then, I’m going to paste my code in there. Let’s make sure there’s a comma at the end of the line.
Next, I’m going to highlight and copy my parameter name.
Then I’m going to add that parameter here.
Note that I have to add a comma to the end of the existing line first, then I have to declare this parameter to be optional. Then I can place WDStartNum and add the type.
I’ve added the parameter and I’ve also embedded the logic. But I also need to make sure I get the expected results. So I’m going to look for the line of code that covers the day of the week.
Then, I’m going to add + WDStart.
I’ll click Done to close the editor. Then, I’m going to invoke the query.
For the StartDate, I’ll put January 1st 2020, then I’ll use December 31st 2020 as the EndDate.
I’ll use “7” as the start month of the Fiscal Year, then I’ll put “0” as the WDStartNum.
I’ll click on Invoke. Now, I have the dates table.
I’ll rename it to make sure it can be easily identified.
Now, I’ll check the results.
This is my DayOfWeek column.
It shows that Monday has returned as 0.
Going back to the question in the Forum, I need to change the number of the start of the week to 1 instead of 0. So I’ll just change that in the measure.
When I press enter, Monday will now be day 1 instead of day 0.
Looking at the number range, it now runs from 1 to 7 instead of 0 to 6.
What happens if I pass a null value?
Then the DayOfWeek for Monday just turns back to 0.
***** Related Links *****
Using The M Function To Create An Extended Power BI Date Table
Beginners’ Guide To The M Code In Power BI
Setting Up A Dynamic StartDate And EndDate For Power Query Date Tables
Conclusion
This is how you can add an additional parameter right into the M function on your date table query. The process is not at all complex, as I mentioned earlier. As long as you have a source for the M code, it’s easy enough from there.
Again, you can always check out the Advanced Data Transformations & Modeling course in Enterprise DNA Online if you have no background on how M codes work.
All the best,
Melissa
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events
[youtube https://youtu.be/oQbGGqPFrUk?rel=0&w=784&h=441]
