Power Query Date Table - pull from data range

[vanessafvg]

I have this great calendar table function by Matt Masson, what would be the best way to automate the date range from the dates in the data?

I am assuming i would need to get the min and max of the date in the data table?  What is the best way to do this in Power Query and then pass it to this function?  

thanks 🙂

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

[stretcharm · Accepted solution]

You can summarise your date table

DateRange

let
    Source = Actual,
    #"Grouped Rows" = Table.Group(Source, {}, {{"MinDate", each List.Min([Date]), type date}, {"MaxDate", each List.Max([Date]), type date}})
in
    #"Grouped Rows"

Then Reference it

I've tweaked the top of the function as follows

let CreateDateTable = (optional Culture as nullable text) as table =>
  let
    StartDate  = DateRange[MinDate]{0},
    EndDate = DateRange[MaxDate]{0},
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),