Forum Discussion

PAPutzback2's avatar
PAPutzback2
Helper II
4 years ago
Solved

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

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

 

 

 

 

Tips and Tricks
  • PAPutzback2's avatar
    PAPutzback2
    4 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.