Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
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
@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.
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".