Forum Discussion

Falcon's avatar
Falcon
Helper I
2 years ago
Solved

Merge and append timeline in multiple rows

Hello,

My data table is similar as below

 

MemberID    ScheduleID    StartDateTime                    StartTimeStamp    StopDateTime                     StopTimeStamp

23                 12                  03/30/2024 06:00:00 AM   1711792800          03/30/2024 06:30:00 AM    1711794600

35                 22                  03/30/2024 06:00:00 AM   1711792800          03/30/2024 06:00:00 PM     1711836000

23                 12                  03/30/2024 06:30:00 AM   1711794600          03/30/2024 07:00:00 AM     1711796400

23                 12                  03/30/2024 07:00:00 AM   1711796400          03/30/2024 10:00:00 AM     1711807200

35                 34                  03/31/2024 10:30:00 PM   1711938600          04/01/2024 06:30:00 AM     1711967400

23                 12                  03/31/2024 10:30:00 PM   1711938600          04/01/2024 06:30:00 AM     1711967400

 

As you noticed "Member ID" 23 with "Schedule ID" 12 have 3 records. The 1st one's "StopDateTime" equals the 2nd one's "StartDateTime" and the 2nd's "StopDateTime" equals the 3rd's "StartDateTime". I'd like to merge these 3 records into one record with the 1st's "StartDateTime" and the 3rd's "StopDateTime". Ideally, I would like this action done in Power Query when we first pull data in. Thanks in advance

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thank you parry2k  for your prompt reply.

    Hi Falcon  ,

    You can open Advanced Editor and follow these steps below:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY9LCoAwDAWvUrouND+b6lWK97+GrW4SqYJCCIQML5PWInFMEak3ApLMmSGUDaDXWCiirlTPwQLsACl92FOLvAzuYxpWR1TuaVfcXG5y2wJ6u1fkJW0KOznwcqBkf2UxNAYiZ7dyNXaScfzqgKLPdv/j9gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MemberID = _t, ScheduleID = _t, StartDateTime = _t, StartTimeStamp = _t, StopDateTime = _t, StopTimeStamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MemberID", Int64.Type}, {"ScheduleID", Int64.Type}, {"StartDateTime", type datetime}, {"StartTimeStamp", Int64.Type}, {"StopDateTime", type datetime}, {"StopTimeStamp", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"MemberID", Order.Ascending}, {"StartDateTime", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index]<=3),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"MemberID", "ScheduleID", "Custom"}, {{"StartDateTime", each List.Min([StartDateTime]), type nullable datetime}, {"StopDateTime", each List.Max([StopDateTime]), type nullable datetime}}),
    #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"StartDateTime", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Custom"})
in
    #"Removed Columns"

    Final output:

    Best Regards,

    Ada Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Falcon you can use group by member id and schedule id, and then add two aggregations in the grouping, min of start date time and max of stop date time, and that will do it.

    • Falcon's avatar
      Falcon
      Helper I
      2 years ago

      Sorry, I only have basic knowledge about Power BI. Can you please provide an example code (or steps) to achieve the result? Thanks

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Falcon transform data -> transform -> group by -> advanced -> member id and schedule id and then add aggregation, see image below:

     

     

    • Falcon's avatar
      Falcon
      Helper I
      2 years ago

      Hello Parry,

       

      Thanks for your suggestion. However, when I tried your solution, it also grouped the bottom record whose timeline was not continuous of the previous ones.

  • Falcon's avatar
    Falcon
    Helper I
    2 years ago

    Can any one help me with this? really appreciate it

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you parry2k  for your prompt reply.

    Hi Falcon  ,

    You can open Advanced Editor and follow these steps below:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY9LCoAwDAWvUrouND+b6lWK97+GrW4SqYJCCIQML5PWInFMEak3ApLMmSGUDaDXWCiirlTPwQLsACl92FOLvAzuYxpWR1TuaVfcXG5y2wJ6u1fkJW0KOznwcqBkf2UxNAYiZ7dyNXaScfzqgKLPdv/j9gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MemberID = _t, ScheduleID = _t, StartDateTime = _t, StartTimeStamp = _t, StopDateTime = _t, StopTimeStamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MemberID", Int64.Type}, {"ScheduleID", Int64.Type}, {"StartDateTime", type datetime}, {"StartTimeStamp", Int64.Type}, {"StopDateTime", type datetime}, {"StopTimeStamp", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"MemberID", Order.Ascending}, {"StartDateTime", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index]<=3),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"MemberID", "ScheduleID", "Custom"}, {{"StartDateTime", each List.Min([StartDateTime]), type nullable datetime}, {"StopDateTime", each List.Max([StopDateTime]), type nullable datetime}}),
    #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"StartDateTime", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Custom"})
in
    #"Removed Columns"

    Final output:

    Best Regards,

    Ada Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.