Power Query and Power Pivot are AMAZING tools, but if you want to create a Calendar Table with either one of them, you’ll either have to learn how to code in M or DAX, or copy/paste a code that you probably found on the web like this one.
The problem with that is that every time that you need to create a Calendar Table you need to go through that whole process, and going through either M or DAX code could be intimidating to even a seasoned player. There’s simply no easy or user-friendly interface or portability, but you STILL need a calendar table if you’re trying to use Power BI’s Time Intelligence functions.
What if there was a single button that could create that Calendar Table for you?
Imagine a button that sits right in the ‘Get Data’ window and once you click it, it’ll ask your for a few parameters like start date of your calendar table and, once you hit OK, you’ll get EXACTLY the calendar table that you were looking for?
Well, this is now possible! and it’s all thanks to the Power BI Custom Connectors.
Not only can you use the Custom Connectors to connect to new data sources, but you can also implement solutions like this one that make things easier for the end-user.
No more copy/pasting. No more long lines of code in either DAX or M.
Just click the button, provide the parameters and you get exactly the table that you want and in your own language!
Watch the Calendar Creator buttons in Action
(Fiscal Calendar Creator Demo)
(Week based Calendar Creator Demo for 4-4-5, 4-5-4, 5-4-4)
Give it a try. Is completely FREE!
Currently the solution can create 2 types of Custom Calendar Tables:
- Calendar Table with Fiscal Columns
- Calendar Table based on the 4-4-5, 4-5-4, and 5-4-4 patterns
Note: the option to add a ISO 8601 Calendar Table will be added within the next few weeks
Here’s a list of steps so you can give this custom connector a try:
Create a [My Documents]\Microsoft Power BI Desktop\Custom Connectors directory
Download the Calendar Creator extension from here and save it in the directory created in step #1
Enable the Custom data connectors preview feature in Power BI Desktop (under File | Options and settings | Custom data connectors)
Restart Power BI Desktop
Find the Calendar Creator buttons in the ‘Get Data’ window
Note: While custom connectors are still in a preview phase, you can’t refresh them on the Power BI Service, but that will change sooner than you think!
Contribute and provide feedback
This project is something that I’ve been working on by myself, but I need your help! Please give this connector a try and provide any suggestions, criticism or report bugs (if there are any!) either in the comment section of this blog post or on Github.
Custom Connectors will become discoverable and available through the Office Store, so you can imagine that one day you won’t even have to download the custom connector from a link, but rather directly from within the Power BI Desktop just like it happens today with Custom Visuals.
Caveat: An Authentication window?
You might have noticed from the videos above that when I try creating a calendar from the custom connector, it requires me to go through an Authentication window like this one:
This is something that I’ve brought up to the Microsoft team and it requires your support so they can get rid of it. Since we are not connecting to any data source, we shouldn’t be prompted for any sort of authentication and in order to get rid of this, we need to go ahead and upvote this idea.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.