Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
gabereal
Frequent Visitor

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

Hello,

I have the following table:

gabereal_0-1686871966659.png

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

 

The desired output should be as follows:

gabereal_1-1686872377359.png

 

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

 

Thanks in advance.

 

 

 

 

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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

View solution in original post

4 REPLIES 4
slorin
Super User
Super User

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

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

gabereal
Frequent Visitor

@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
Super User
Super User

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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors