Those that downloaded the file, had a hard time to modify it (never got a complain, but still….), as it was done without any care from my side. I apologize in advance if you are one of those that downloaded the file!). The main reason why that file looked the way it did, was mainly because doing it properly took too much time, and when I wasn’t able to explain the queries…what was the point?
Well, with the recent Power BI updates, the Power BI team has given us the ability to do a proper job, especially if you are sharing your files with others.
Here is how my calendar file looks today:
And it self-generates!!! (with some basic input from your side to fit your needs).
A good calendar, should be properly formatted, so here are my new new best practices when working in the query editor/power query: ( I say new because you need the latest update to use some of the features)
Rename all the query steps so other uses can quickly find them and modify them.
First of all, we needed to clean up the query steps, give them proper names.
There is more than one way to do this; Right-click > Rename or Right-click > Properties –(new with June update) but I prefer to click on the step and then press F2, it is faster.
2. Select the correct Data type for all columns
If you have been lazy like me, you might have left some data types default and got into trouble when trying to display the values in graphs.
But you haven’t missed the icons on the left of every power bi column right? With them, you can see easily see if a column has been left default and also you can change it to the right value easily.
I normally try to this transformation at the end in one step as it makes the file more readable. If I need to change the data type to reference it on another column, I use Number.ToText function.
I want to have leading zeros on my Day column. The function Date.Day returns a number, but instead of converting that into a number and then create the leading zeros I can do that in one step with the Number.ToText function:
To add comments to your query steps, select the step and then Right-click > Properties. If you are adding a lot of steps, it might be easier to do it in the advance query editor. Read Chris webb post to learn how to do it.
And the previous step leads us to this step which makes the calendar easily configurable:
5. Add query parameters to easily customize your calendar
Let’s use the previous example to explain this one. In the previous step, we hardcoded the language code, but why not using query parameters to easily change those values so the users don’t need to look for them?
I have created 4 query parameters to configure the calendar to different regions and dates.
This calendar uses List.Date to generate the date table. The values to specify the dates you want your calendar to be are hardcoded in the Invoke step, so I created parameters for that: (Year, Month, Date). There is also a parameter to configure if your week starts on Monday or Sunday.