Create list of date ranges from a list of dates using power query

0
votes

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.

2
powerbipowerquerym
Welcome to SO! As this is not a free code writing service, please show what you have tried so far and where it failed. You might also want to read stackoverflow.com/help/minimal-reproducible-example and stackoverflow.com/help/how-to-ask. – Binarus

2 Answers

1
votes

This function, which I called Parse Dates, should do it:

(dateList) =>
let
#"Converted to Table" = Table.FromList(dateList, Splitter.SplitByNothing(), {"Dates"}, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Dates", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Added Start" = Table.AddColumn(#"Added Index", "Start", each try if #"Added Index"{[Index]-1}[Dates] = Date.AddDays([Dates],-1) then null else [Dates] otherwise [Dates]),
#"Added End" = Table.AddColumn(#"Added Start", "End", each try if #"Added Start"{[Index]+1}[Dates] = Date.AddDays([Dates],1) then null else [Dates] otherwise [Dates]),
#"Added Custom1" = Table.AddColumn(#"Added End", "Group", each "Group"),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Group"}, {{"Start", each List.RemoveNulls([Start]), type anynonnull}, {"End", each List.RemoveNulls([End]), type anynonnull}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Tabled", each Table.FromColumns({[Start],[End]},{"Start","End"})),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Tabled"}),
#"Expanded Tabled" = Table.ExpandTableColumn(#"Removed Other Columns", "Tabled", {"Start", "End"}, {"Start", "End"}),
#"Added Custom3" = Table.AddColumn(#"Expanded Tabled", "Custom", each List.Dates([Start],Number.From([End]-[Start])+1,#duration(1,0,0,0))),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"Custom"})
in
#"Removed Other Columns1"

I invoked it with this:

let
Source = #"Parse Dates"(#"Dates List")
in
Source

...against this list, which I called Dates List:

enter image description here

...to get this result:

enter image description here enter image description here enter image description here enter image description here

1
votes

I managed to figure out how to use the List.Generate function to solve this problem. This function works a little faster for me. I called it fn_ListOfDatesToDateRanges. To invoke it, you must pass a column in each row of which there is a list of dates. Information from the KenR blog helped me with development. To compare performance, I used an array with about 250 thousand lines. The increase in speed was 45 seconds versus 1 minute ~ (-33%)

Test file with used functions is here 

    (Dates)=>
let
InputData = List.Transform(List.Sort(Dates,Order.Ascending), each DateTime.Date(DateTime.From(_, "en-US"))),
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