cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Power Query Date Table - pull from data range

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 Smiley Happy

 

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
1 ACCEPTED SOLUTION

Accepted Solutions
stretcharm Senior Member
Senior Member

Re: Power Query Date Table - pull from data range

 

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)),
1 REPLY 1
stretcharm Senior Member
Senior Member

Re: Power Query Date Table - pull from data range

 

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)),