Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

ruthpozuelo

The "only" Power BI calendar you will ever need

 

Tired of creating Power BI calendars? Well, me too, so let's put a stop to that.

 

In this blog post you will learn how to create (or download mine) a proper calendar that ”self-generates” when you open the file and is configurable.

If you download mine, you most likely will need to add columns that are relevant to you, but using the methodology explained in the post, you will do it only once.

 

Note: You need to have the latest (june 2016) power bi update installed.

 

First, some of my "new" best practices with Power Query

 

I have been sharing a calendar in my YouTube channel to help others create their calendars fast, but I can’t say I was proud of that work. Here is how it looked like:

 

1.calendar before.png

 

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:

 

2 calender after.png

 

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)

 

  1. 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.

 

3 rename query steps.png

 

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.

 

For example:

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:

 

Text.PadStart(Number.ToText(Date.Day([Date])),2,"0"))

 

The file becomes more readable without all those steps in the middle.

 

4. group chages if possible.png

 

 

3. Reorder your columns to make the file more readable.

 

For example, in our calendar example, some of the steps create date columns and others create sort columns. I have regrouped them so they are next to each other.

 

You haven’t missed the new functionality that allows you to drag and drop the query steps right? Live saver!!

5 group similar queries

 

4. Add comments to your query steps to help others understand or modify your query

 

This is a new feature from the June (2016) update and here is a cool example on when this make sense:

Our calendar has a Day Name and Month Name column. Depending on which country you are at or how the report will be used, you might want those in your local language or not.

 

Power Query allows you to change the language by adding a country code at the end:

 

Date.ToText([Date],"ddd", "en-US"))

 

Now, if somebody downloads the calendar from me again, I want them to be able to modify this easily, so a proper text could be:

 

"Extract Day Name from Date column using American names. Navigate here for other Languages not included in the parameters: https://msdn.microsoft.com/en-us/goglobal/bb896001.aspx"

 

 6. query comments.png

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?

 

Date.ToText([Date],"ddd",#"Language")

 

I have created 4 query parameters to configure the calendar to different regions and dates.

 

7. query parameters.png

 

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.

 

And now the icing on the cake.

 

Save your calendar as a Power BI Template

 

How about saving this as a Power BI template and use this as a new file instead of an ordinary one? I learned this from Ryan Dusty and you can check out other tips and tricks from him here.

 

It would be great if we we could save our templates as shortcuts on the Power BI ribbon....

 

So, if you save this as a template, when a user opens the file, this is what they get:

 

9 ask for query parameters.png

 

Isn’t that awesome?

 

You might need to modify this file to be able to use it, but once you created it, you won’t need to build another power bi calendar!!

 

Here is a demonstration of how the calender looks like and you will find there the link to download too.

 

10 youtube.png

 

Happy Analyzing!

Comments