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

drigloff
Posts: 2
Joined: Thu Nov 07, 2019 5:47 am

### Create list of date ranges from a list of dates.

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

drigloff
Posts: 2
Joined: Thu Nov 07, 2019 5:47 am

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

I figured this out. Description of solution and file by [url=https://stackoverflow.com/questions/59165201/create-list-of-date-ranges-from-a-list-of-dates-using-power-query/59336691#59336691] link[/url]
[code]
(Dates)=>

let

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 =

let

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

End_Date=(

if NextRowDate = null then CurrentRowDate else

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

]

in

MyDate,

Counter=[Counter]+1],

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"})

in

DateRanges
[/code]