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.

Postby drigloff » Thu Nov 07, 2019 5:57 am

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.

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

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

Postby drigloff » Sun Dec 15, 2019 3:27 am

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]


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest

cron