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

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. 

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.