Simple calendar table creation with M Query with date range from fact table

[PAPutzback2]

I can never seem to find the post that covers how to create a calendar table with M, it is either DAX or some strange functions that need to be invoked, or a dummy table needs to bet up with seed dates...
I have a lot of reports that just need a date slicer. I also have users that want their calendar to show the 1st day and last day of the year even if there is no data that relates to those dates.

let
Source = List.Dates(StartDate, DurationDays, #duration(1, 0, 0, 0)),
StartDate = Date.StartOfYear(List.Min(#"MY FACT TABLE"[MY DATE COLUMN])),
EndDate =Date.EndOfYear(List.Max(#"MY FACT TABLE"[MY DATE COLUMN])),
DurationDays = Duration.Days(EndDate - StartDate)+ 1,
ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Column1 to Date" = Table.RenameColumns(ToTable,{{"Column1", "Date"}}),
#"Changed Data Type From Text To Date" = Table.TransformColumnTypes(#"Renamed Column1 to Date",{{"Date", type date}})
in
#"Changed Data Type From Text To Date"

 If you want to start and end the calendar on the min and max date in your fact table you can remove the function Date.StartOfYear and Date.EndOfYear. Dont forget the to remove the right paren.

If you need additional calendar columns you can do it from the menu

This page has M code examples for building various periods and metadata based on the Date column if you want to copy and paste them in after the last step in the code above (#"Changed Data Type From Text To Date"). Rename the step referenced in the first step copied in to #"Changed Data Type From Text To Date". Also make sure your last "in" statement references the name of the last step in the let statement.

Build a Reusable Calendar Table with Power Query ⚡ - Excelerator BI

[PAPutzback2 · Accepted solution]

Thanks. I knew I could hard code the date in the query, but that would require opening the model to update the date if you had to change it. Perhaps you had patient data and the calendar was tied to birthdate and an older person was added, or housing data and earlier build dates start showing up. 

[v-stephen-msft]

Hi @PAPutzback2 ,

Try this

let
    Source = #date(2019, 1, 1),
    #"add dates until today" = List.Dates(Source, Number.From(DateTime.LocalNow())+0- Number.From(Source), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(#"add dates until today", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Column Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type of Column Date" = Table.TransformColumnTypes(#"Renamed Column Date",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type of Column Date", "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),
    #"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month Name", each Text.Start(_, 3), type text}}),
    #"Inserted Day of Week" = Table.AddColumn(#"Extracted First Characters", "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)
in
    #"Inserted Day Name"

You can manually change the maximum and minimum dates in the fact table in the formula.

If the minimum date is January 1, 2021, and the maximum date is December 31, 2021, modify it to

let
    Source = #date(2021, 1, 1),
    #"add dates until today" = List.Dates(Source, Number.From(#date(2021, 12, 31))+0- Number.From(Source), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(#"add dates until today", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Column Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type of Column Date" = Table.TransformColumnTypes(#"Renamed Column Date",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type of Column Date", "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),
    #"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month Name", each Text.Start(_, 3), type text}}),
    #"Inserted Day of Week" = Table.AddColumn(#"Extracted First Characters", "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)
in
    #"Inserted Day Name"

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PAPutzback2 · Accepted solution]

Thanks. I knew I could hard code the date in the query, but that would require opening the model to update the date if you had to change it. Perhaps you had patient data and the calendar was tied to birthdate and an older person was added, or housing data and earlier build dates start showing up. 

[v-stephen-msft · Accepted solution]

Hi @PAPutzback2 ,

Based on my research, here's my solution

let
    Source = "",
    MinDate = List.Min(MainTable[Date]),
    MaxDate = List.Max(MainTable[Date]),
    Days = Duration.Days(MaxDate-MinDate)+1,
    Duration = #duration(1, 0, 0, 0),
    Custom1 = List.Dates(MinDate,Days,Duration),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}})
in
    #"Renamed Columns"

This code gets the maximum date and minimum date from the main table to create a calendar table, hope it can help you.

Reference: https://foresightbi.com.ng/microsoft-power-bi/creating-calendar-tables-with-power-query/

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.