Page 1 of 1

Create list of date ranges from a list of dates.

Posted: Thu Nov 07, 2019 5:57 am
by drigloff
Please help me create a function in Power Query.
At one of the steps of the query, as a result, I get a list of dates. Some go sequentially, some separately. The quantity is not fixed.
Example (MM.DD.YYYY):
{01/01/2019, 01/02/2019, 01/03/2019, 01/05/2019, 01/06/2019, 01/08/2019}
I need to determine all intervals of consecutive dates and reflect the list of such intervals. The interval is set by the start and end dates. If there is one continuous date, then it is the beginning and the end.
An example from the previous data:
{{01/01/2019, 01/03/2019}, {01/05/2019, 01/06/2019}, {01/08/2019, 01/08/2019}}.
Please help me write a function to solve this problem.
In my data there are about 10,000 lines, each of which has a list attached up to 365 days. It is desirable that the function works quickly.
It feels like list.generate can help, but I don't understand this function very well.

Re: Create list of date ranges from a list of dates.  [Solved]

Posted: Sun Dec 15, 2019 3:27 am
by drigloff
I figured this out. Description of solution and file by [url=] link[/url]


InputData = List.Transform(List.Sort(Dates,Order.Ascending), each DateTime.Date(DateTime.From(_, "ru-RU"))),

DateRangesGen = List.Generate(

()=> [Date=null, Counter=0],

each [Counter]<=List.Count(InputData),

each [

Date =


CurrentRowDate = InputData{[Counter]},

PreviousRowDate = try InputData{[Counter]-1} otherwise null,

NextRowDate = try InputData{[Counter]+1} otherwise null,

MyDate = [Start_Date=

(if PreviousRowDate = null then CurrentRowDate else

if CurrentRowDate = Date.AddDays(Replacer.ReplaceValue(PreviousRowDate,null,0),1) then null else CurrentRowDate),


if NextRowDate = null then CurrentRowDate else

if CurrentRowDate=Date.AddDays(Replacer.ReplaceValue(NextRowDate,null,0),-1) then null else CurrentRowDate)





each [Date]),

DateRanges = Table.ExpandTableColumn(Table.SelectColumns(Table.AddColumn(Table.Group(Table.AddColumn(Table.ExpandRecordColumn(Table.FromList(DateRangesGen, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"Start_Date", "End_Date"}, {"Start_Date", "End_Date"}), "Group", each "Group"), "Group", {{"Start_Date", each List.RemoveNulls([Start_Date]), type anynonnull}, {"End_Date", each List.RemoveNulls([End_Date]), type anynonnull}}), "Tabled", each Table.FromColumns({[Start_Date],[End_Date]},{"Start_Date","End_Date"})),{"Tabled"}), "Tabled", {"Start_Date", "End_Date"}, {"Start_Date", "End_Date"})