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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors