How to create a timeline of records without overlap and splitting records

[gabereal]

Hello,

I have the following table:

I would like to create a new table that will create a timeline without overlap.

The desired output should be as follows:

The index column determines the hierarchy of two records. The lower index records takes precedance. 

Thanks in advance.

[slorin · Accepted solution]

Hi

Another solution without testing minutes by minutes

let
Source = YourSource,
Time_List = List.Sort(List.Distinct(Source[From]&Source[To]), Order.Ascending),
Time_Table = Table.FromColumns({List.RemoveLastN(Time_List,1), List.RemoveFirstN(Time_List,1)},{"From","To"}),
Fn_Filter = (my_table,my_time) => Table.SelectRows(my_table, each [From] <= my_time and my_time < [To]),
TableMin = Table.AddColumn(Time_Table,"Data",each Table.Min(Fn_Filter(Source,[From]),"Index")),
Expand_Data = Table.ExpandRecordColumn(TableMin, "Data", {"ID", "Index"}, {"ID", "Index"}),
Sort = Table.Sort(Expand_Data,{{"From", Order.Ascending}}),
LocalGroup = Table.Group(Sort, {"ID", "Index"}, {{"From", each List.Min([From]), type datetime}, {"To", each List.Max([To]), type datetime}}, GroupKind.Local)

in
LocalGroup

Stéphane

[gabereal]

This solution seems to output the desired result without any changes required. Thanks for your help @slorin  and @lbendlin.

[gabereal]

@lbendlin Thanks for the response. This solution is very close. Had to make some adjustments in order to implement on a minute by minute basis. Also, there was an issue with the #"Grouped Rows" method where some guids were incorrectly obtained using the List.Min([ID]). Fixed by ensuring the Index# was 1:1 with the ID key. Then obtaining the key that way. Thanks for your help.

[lbendlin]

Thank you for this nice challenge.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/NTDFU0lEy0zc00DcyVjCzMjBIzIUIGIIEDI1gIgZKsToQDUZIGozRNcAFDOHqjZHUWwClC5DVm8LUG8HVmxDnIGOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, From = _t, To = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"To", type datetime}, {"From", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Hours", (k)=> List.Generate(()=>k[From],each _ < k[To],each _ + #duration(0,1,0,0))),
    #"Expanded Hours" = Table.ExpandListColumn(#"Added Custom", "Hours"),
    #"Grouped Rows" = Table.Group(#"Expanded Hours", {"Hours"}, {{"Index", each List.Min([Index]), type nullable number}, {"ID", each List.Min([ID]), type nullable text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Hours", Order.Ascending}}),
    #"Grouped Rows1" = Table.Group(#"Sorted Rows", {"ID"}, {{"From ", each List.Min([Hours]), type datetime}, {"To", each List.Max([Hours])+#duration(0,1,0,0), type datetime}, {"Index", each List.Min([Index]), type nullable number}},GroupKind.Local)
in
    #"Grouped Rows1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".