The "only" Power BI calendar you will ever need

by ruthpozuelo on ‎09-07-2016 09:54 PM

 

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
by Brian_M
on ‎09-08-2016 12:59 AM

Super post Ruth. A great reminder of some new features and you've pulled together those best practices nicely! 

by ruthpozuelo
on ‎09-08-2016 01:31 AM

Thanks @Brian_M!

Smiley Happy

/Ruth

by ThomasTurbado Frequent Visitor
on ‎09-08-2016 03:18 AM

 Super post Ruth. It's amazing! I try to do the same by my own , but it is very complicated for a beginner like me so I copy it. Nice job

by ruthpozuelo
on ‎09-08-2016 03:55 AM

Thanks @ThomasTurbado! Smiley Happy

 

/Ruth

by yoshihirok Member
on ‎09-08-2016 06:59 PM

Excellent

by ado Visitor
on ‎09-20-2016 06:21 PM

Great post. Thanks Ruth.

by ruthpozuelo
on ‎09-22-2016 02:57 AM

Thanks to everybody for all the comments and kudos!  Smiley Happy

/Ruth

by cnewell7 Frequent Visitor
on ‎10-06-2016 10:14 AM

Hi Ruth, can we have this adapted to deal with custom calendars like retail 4-4-5 etc?

by ruthpozuelo
on ‎10-06-2016 12:33 PM

Hi cenewell, 

It should be possible. What are your rules for the calendar? I can give it a try!

/Ruth

by cnewell7 Frequent Visitor
on ‎10-06-2016 12:56 PM

Hi Ruth, it's a retail calendar arranged in 4-4-5 format

There is additional complication in dealing with with leap years

Here is a definition:

https://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar

by ruthpozuelo
on ‎10-07-2016 08:05 AM

ok, so a "standard" 4-4-5 calendar works fine for you? Give me a few days and I let you know.

/Ruth

by BryantAvey Occasional Visitor
on ‎10-07-2016 12:35 PM

This is really great way to make your date dimension table.  Love the technique, and may even adopt it over some of my other methods. At least this is a really nice post that I can refer people to, who are struggling with it.  I really like your naming convention using the "sort" columns, etc.  I never know what to call mine, and really enjoyed seeing how you handled it.

 

Thanks for the post.

by ruthpozuelo
on ‎10-10-2016 01:31 AM

HI Bryant,

As english is not my native language I often struggle to find good names to my columns too!

Thanks for taking the time to give me that detailed feedback, I really appreciate it Smiley Wink

/Ruth

by ruthpozuelo
on ‎10-10-2016 01:28 PM

Hi @cnewell7,

 

I created a calendar template that generates different custom calendar types: 445 calendar, 454 and 544. Check it out and let me know if that is what you wanted to do!

 

https://www.youtube.com/watch?v=JqVnqMLGWDY&feature=youtu.be

 

/Ruth

by dbadmin Member
‎01-05-2017 08:42 AM - edited ‎01-05-2017 10:28 AM

Hey Ruth! Smiley Happy Thanks so much for this! Works great. Keep up the great work. Enjoy your blogs also!

by ruthpozuelo
on ‎01-06-2017 09:36 AM

Hi @dbadmin!

Thanks! Smiley Happy 

Really happy to be able to contribute to this amazing community!

Happy New Year!

/Ruth

by jason85 Occasional Visitor
on ‎02-15-2017 01:42 AM

It's important to use other type of calendars as well such as monthly calendars which will help in managing your time an work activities. Download March Calendar 2017, April 2017 Calendar and more.

by ruthpozuelo
on ‎01-22-2018 01:47 AM

Thanks @Sanjeev787! Glad you found it useful Smiley Happy

/Ruth

by satnam Visitor
on ‎01-23-2018 09:07 AM

Very well done this is amazing, and please check ones these useful calendars

 

February 2018 Calendar

Printable 2018 Calendar

We hope this month will amaze you.

 

by satnam Visitor
on ‎01-29-2018 02:42 AM
by satnam Visitor
on ‎01-29-2018 02:43 AM