Creating A Custom Calendar Table With Power Query

Matt Allington shows how to create a calendar table which allows users to set the start and end dates:

My approach to teaching people to use Power Query is to always use the UI where possible.  I first use the UI to do the hard work, then jump in and make small changes to the code created by the UI to meet any specific variations required.  Keep this concept in mind as you read this article.

I am going to use Power BI Desktop as the tool for this, but of course Power Query for Excel will work just as well and the process is identical.  In fact the calendar query at the end can easily be cut and pasted between Power BI and Power Query for Excel.

Check it out for another method for building calendar tables.  I tend to build them in SQL Server because that’s what I’m most familiar with, but it’s good to know a few different ways of doing this.

Related Posts

Blank Rows and DAX

Alberto Ferrari explains how different DAX functions treat blank rows differently: DAX offers two functions to retrieve the list of values of a column: VALUES and DISTINCT. The difference between the two is subtle. To understand it better, we first need to introduce the concept of the blank row. The blank row is a special row added […]

Read More

Power Query and the Benefits of Immutability

Chris Webb explains why immutable expressions can be faster to run multiple times than mutable processes: Instead of taking the value #”Sorted Rows”[Column2]{0} and storing it in the variable Column2 then adding Column2 four times, I’m  adding the expression #”Sorted Rows”[Column2]{0} together four times. The query returns the same number as the previous query. However […]

Read More

Categories

April 2018
MTWTFSS
« Mar May »
 1
2345678
9101112131415
16171819202122
23242526272829
30