Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
https://github.com/nolockcz/PowerQuery/tree/master/Date%20Dimension However, I personally recommend reading the article once before you use it in your project.
We all know how important it is to transform our data into a star schema and to have a fact table and dimension tables. Date dimension is one of those dimensions.
There are 4 ways how we can create a date dimension:
There are pros and cons of all these solutions. In my scenario, I need to have a date dimension already in PowerQuery because it is used in my ETL process. I’ve been searching on the internet for solutions and have found some:
Unfortunately, all these contain only the basics and therefore didn’t fulfill my expectations. At this moment, I decided to write my own date dimension in PowerQuery from scratch.
A good date dimension used later in Power BI must start on the 1st of January and end on the 31st of December. Therefore, I personally prefer to use only the start and the end year as parameters.
There is a long list of features I have implemented: years, half-years, quarters, months, ISO weeks, days, and their differently formatted variations.
There is also a group of columns calculating a count of years backwards, vice versa for quarters, months, weeks, and days. What is it good for? You can filter your fact table by a range like MonthsBackwards <= 13 AND MonthsBackwards >= 1 to get data from the last 12 full months.
And finally, the holy grail - holidays! In many business use cases, it is crucial that we know if a definite date is a working day or not. And what is a working day? It is a day which is neither weekend nor an official holiday. Countries (or even states within a country) have their own holidays. How have I solved this diversity? I have not yet. I have prepared the dimension for holidays in the state of Baden-Württemberg in Germany. And it is your task to check out the list and remove or add new holidays – it is very simple, trust me.
How to modify the list of holidays? There is a function called fnGetAllHolidaysOfAYear which generates all holidays in a year. In Germany, most of them are based on the Easter Sunday and that is also my starting point. Then I generate a list CurrentYearHolidaysList which contains the definition of all holidays in one year.
Example New Year and Easter Monday:
// NewYear = always January 1st [Date = #date(year, 1, 1), HolidayName = "Neujahr"],
// EasterMonday = 1 day after Easter Sunday [Date = Date.AddDays(EasterSunday, 1), HolidayName = "Ostermontag"],
The last thing I do is translating all column names. Now you are back in the game again. It is up to you to change these translation pairs.
There will never be a date dimension which fulfills everybody’s expectations. But we will do our best! Right?
I have committed 2 versions (https://github.com/nolockcz/PowerQuery/tree/master/Date%20Dimension😞
Please check out the code from GitHub, hack, and report bugs and ideas for improvement. Every constructive feedback is very welcomed.
I hope that the community together can create a new etalon of a date dimension in PowerQuery!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.