In this blog post, we’ll explore my solution to Problem of the Week #12. For this challenge, we revisited Enterprise DNA’s maiden Problem of the Week challenge, where we created a perpetual holiday calendar from a mix of specific and relative dates. But this time around, we will be using only Power Query. You can watch the full video of this tutorial at the bottom of this blog.
If you’re stuck on the problem breakdown itself, you can revisit the first iteration to see if there are elements in any of the approaches that can help you with this challenge.
While reading the submissions, I’ve seen a mix of people leveraging the user interface and writing custom functions to create a perpetual calendar. It’s been enjoyable to analyze each entry, and if you want to get inspired, I encourage you to do the same.
When I was working through this scenario myself, I ended up creating multiple solutions. The one I went with best illustrates how you can not only break down a problem into smaller pieces but also make them visible. This is very helpful if you encounter an error or unexpected results.
The Starting Point For Creating A Perpetual Calendar
This raw holiday table is our starting point. To piece together a date, we need a year, a month, and a day for specific dates. All that’s missing is the year.
But for relative dates, we need to do a little bit more work. For starters, we need to split that day column into both the numeral and the weekday. Let’s review those steps.
Retrieving The Years From The Dates Table
To make this solution dynamic, I’m going to retrieve the years from the dates table. In the formula bar, you can see that I’ve used a projection to get a single column table from my date table. This, of course, will contain duplicates. To remove those, I’ve added Table.Distinct.
When I click off to the side in the white space beside any of these tables, you’ll also see a list with the distinct years present in my date table query.
We were given a month name, but we will actually need a month number. Now, when we concatenate a year to a month name, we can use the Date.From function to return the first of that month as a date value.
By adding Date.Month, we’re left with just the month number for that specific date. For January, it returns a 1. For February, it returns a 2, and so on.
Getting The Numeral From The Day Column
To get the numeral from the day column, we decided not to split the column and extract the value instead. As you can see in the formula bar, we used Text.BeforeDelimiter and used that space to identify the right and left part of that text string.
If there’s no space, we want to return whatever remains in the day column. So for the first record, that returns a 1. For the second record that contains that space, it returns the first part of the text string, the word Third.
We will use a similar approach to get the date name, but use the function List.PositionOf. This returns the zero-based offset of the value in the list passed as first argument, and it returns -1 if the value doesn’t appear in that list.
For the first record, it passes the value 1, which isn’t present in our list. This is why it returns the value of -1. For the second record, it returns the value Monday.
It passes that to our function and returns a 1 because it’s the second item in our list. Next, we’ll replace all -1 values with a null.
At this point, we have created all of the building blocks we need to invoke the custom function.
Invoking The Custom Function For The Perpetual Holiday Calendar
In the formula bar, we can see that the function takes four arguments: a year, a month, a numeral, and a weekday.
But how does it determine that date value? We’ll use the List.PositionOf again to get a numeric value for the numeral. We have a list containing first, second, third, and fourth. When we pass the numeral to the List.PositionOf, it returns the zero-based offset.
Again, if a value is not present, it returns a -1. So for the first record, we’re passing the value 1. 1 is not present in this list, which is why it returns the value of -1. For the second record, we’re passing the value Third, which is the third item in our list. This returns the value of 2.
Creating A List Of Dates
Let’s now create a list of dates. We will determine the start date for that month, determine the number of days in that month, and use List.Dates to create a list containing all the days in that month.
Next, we will use List.Select to only retain those dates that are on a specific weekday.
When we click off to the side beside any of these lists, we can see that it only contains these four items.
Applying Conditional Logic
Finally, we will apply conditional logic to construct or retrieve a date. If the weekday is null, indicating we have a specific date, we can use the intrinsic #date to construct a date value.
If the numeral equals last, then we can use the List.Reverse function to reverse the order in the list and extract the first value.
If we want to change this ascending order into descending, we can always extract the last date from that range of dates. It does not matter if we have four or five elements in the list. By reversing the order and extracting the first item, we always have the last date.
For all the other cases, we use List.Skip.
We can extract the value based on this position on the list. For Thanksgiving day, the numeral is fourth, and fourth returns a 3. This will skip the first three values in the list, and will always return the fourth item.
Let’s put it all together and go to the solution query. For the source step, we’re calling the RAW holidays date table. Next, we’ll add a column that returns a list with dates.
Reviewing The M Code For The Perpetual Holiday Calendar
Let’s check the M code for the column. For each row in the table, we nested a table containing the distinct years from the dates table. Then we transformed the values in that year column by calling the custom function.
Of course, to be able to invoke the custom function, we have to do some setup first. We have to get the month number, the numeral value, and of course, the weekday. Then from that nested table, we kept the list with dates.
When we click off to the side in the white space, we can see a preview of that list down below.
The next step is to expand on that list and add the CelebratedOnDate.
This is the logic we used in this calculation: if the date is on a Saturday, then we’re going to subtract a day so that we land on a Friday. If it’s a Sunday, then we’re going to add a day to land on a Monday. If it’s not a Saturday or Sunday, then we want a null.
Finally, the last step is to change the types.
***** Related Links *****
Power BI Holiday Calendar – Calculate Date W/DAX
Creating A Calendar Table For Holidays In Power BI
Time Comparison For Non Standard Date Tables In Power BI
Conclusion
Just a quick reminder that Problem of the Week is an ongoing series that happens every 1st and 3rd Wednesday on the Enterprise DNA forum. The problem posted on the 1st week requires a DAX-based solution, while the 3rd week problem asks for a Power Query-based one.
If you enjoyed the content covered in this POTW, please subscribe to the Enterprise DNA TV channel for more. We have a huge amount of content coming out all the time from myself and a range of content creators, all dedicated to improving the way that you use Power BI and the Power Platform.
Melissa
