Merge and append timeline in multiple rows

[Falcon]

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

[v-yifanw-msft · Accepted solution]

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.

[Falcon]

Thanks so much

[Falcon]

Can any one help me with this? really appreciate it

[parry2k]

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

[Falcon]

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.

[parry2k]

@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]

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