How to “auto” generate calendar tables with Power Query – The best method

automatic calendar table with power query

Calendar (or date) table is crucial for performing date intelligence calculations in Power BI. 

Normally, you would find a reasonable calendar table in most data models. But occasionally I come across models where there is no calendar table. 

So I present to you, the ultimate & best way to generate calendar table using Power Query.

How to create the perfect calendar table with Power Query?

Start by creating a new “blank query” connection in Power BI.

blank query option in Power BI (PQ)

Then use the =List.Dates() function to generate the dates you want.

For example, to get the calendar dates for year 2023 use below code:

				
					= List.Dates(#date(2023,1,1),365, #duration(1,0,0,0))

This will generate a list of all the dates in 2023. 

Now, convert the list to a table using the List Tools > Transform ribbon. 

convert list of dates to a table

Once you have the dates in a table format, you can use the “add column” ribbon and “Date” options to introduce many date related columns.

For example you can add:

  • Year (4 digit year value)
  • Month number
  • Month name
  • Weekday name
  • Weekday number
  • Start of month
  • End of month
  • Start of week
  • Quarter of the year
  • Days in a month

Adding date calculations with Power Query

Additional “smart” date columns

Apart from all the columns above, I normally add these two columns to my calendar tables.

  • Year month (a 6 digit representation like 202308 for ex.)
  • Type of month (current month, previous month, next month)

Let’s look at the Power Query (M) code for these columns.

Year Month (yyyymm):

 

				
					= Table.AddColumn(#"Inserted Start of Month", "Year Month", each [Year] * 100 + [Month])

Type of Month:

For this we need to do a few steps.
  1. Calculate the current date’s Year Month (yyyymm) using the below M code:

 

				
					=Table.AddColumn(#"Added Custom", "Current Month", each let cm = Date.StartOfMonth(DateTime.LocalNow()) in Date.Year(cm) * 100 + Date.Month(cm))

  1. Then subtract this value from each date’s year month
  2. That number will be 0 for current month, -1 for previous month, 1 for next month etc.
  3. Then use the “conditional column” to set month type accordingly.

Refer to the full M code script below for the entire calendar table in one go.

Full M Script for generating the calendar table

Use this M script to generate the calendar table for year 2023. 

To apply this, create a blank query in PQ and then go to View > Advanced Editor and paste the code there. Adjust the year in source step (step 1) to get the calendar for any year.

				
					let
    Source = List.Dates(#date(2023,1,1),365, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Is Weekend?", each if [Day of Week] = 6 then "Yes" else if [Day of Week] = 0 then "Yes" else "No"),
    #"Inserted Start of Month" = Table.AddColumn(#"Added Conditional Column", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Added Custom" = Table.AddColumn(#"Inserted Start of Month", "Year Month", each [Year] * 100 + [Month]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Current Month", each let cm = Date.StartOfMonth(DateTime.LocalNow())
in Date.Year(cm) * 100 + Date.Month(cm)),
    #"Inserted Subtraction" = Table.AddColumn(#"Added Custom1", "Subtraction", each [Year Month] - [Current Month], type number),
    #"Added Conditional Column1" = Table.AddColumn(#"Inserted Subtraction", "Month Type", each if [Subtraction] = 0 then "This Month" else if [Subtraction] = 1 then "Next Month" else if [Subtraction] = -1 then "Previous Month" else "Other Month"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Current Month", "Subtraction"})
in
    #"Removed Columns"

Perfect Calendar with Power Query – Video

Check out this video to understand the process better.

More ways to make the calendar table

Check out below tutorials from other Power BI / Excel folks to see different Power Query scripts.

  • All in one script for calendar table – Radacad
  • Parameterized Date Tables – BI Gorilla
  • Auto generate calendar table with MonkeyTools add-in

The post How to “auto” generate calendar tables with Power Query – The best method appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: https://chandoo.org/wp/power-query-calendar-table-best-method/

Tags: , ,
Close Menu