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 dat...
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.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
PAPutzback2
Helper II
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
    Not applicable
    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.