Merge two rows based on certain conditions

Hi Anonymous ,

I create a sample data myself:

Then you can create a blank query and put all of the M code below into the advanced editor:

let
    MergeRows = (inputTable as table) as table =>
    let
        AddIndex = Table.AddIndexColumn(inputTable, "Index", 1, 1, Int64.Type),
        RecordsList = Table.ToRecords(AddIndex),
        MergeFunc = (rows as list, merged as list) =>
            let
                current = List.First(rows),
                remaining = List.RemoveFirstN(rows, 1),
                newMerged = 
                    if List.Count(merged) = 0 then
                        {current}
                    else
                        let
                            lastMerged = List.Last(merged),
                            timeDiff = Duration.TotalMinutes(current[Event Frame Start Time] - lastMerged[Event Frame End Time])
                        in
                            if timeDiff < 60 then
                                List.RemoveLastN(merged, 1) & {[
                                    Index = lastMerged[Index],
                                    Event Frame Start Time = lastMerged[Event Frame Start Time],
                                    Event Frame End Time = current[Event Frame End Time],
                                    #"T-1002 Level Drop" = lastMerged[#"T-1002 Level Drop"] + current[#"T-1002 Level Drop"]
                                ]}
                            else
                                merged & {current}
            in
                if List.Count(remaining) = 0 then
                    newMerged
                else
                    @MergeFunc(remaining, newMerged),
        MergedList = MergeFunc(RecordsList, {}),
        ResultTable = Table.FromRecords(MergedList),
        RemoveColumns = Table.RemoveColumns(ResultTable, {"Index"})
    in
        RemoveColumns,
    OutputTable = MergeRows(Table) // Make sure to replace "Table" with your actual table name
in
    OutputTable

And the final output is as below:

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

Please post some sample data of your problem and of the result you need

Hi Anonymous 

Try this code

it can help you in this problem easily by adding an index column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZE7DsMwDEOvEmQOEEmWHJtbD1Cge5D7X6N2LX86GeADIZK+713CyXwKiW4sYILE7fXejz3QAApNIKv6c9wrSVCDuSOeLE02yAWj7dMMK1BycOxF7LqAYjneDQsJkADN7rChXxADy3BMksARdDVHred6BgnK6y0WUopTSabNUuuNI8XCf8W7pdSIsOBbnfQbxGrcXAfxWBMoiLxh6cFDz77tbN5JTVVW5HaC8wQMUnDoHqGJKhFpFtElVrgQRqw/YglmvlZaclHyn32eLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Event from" = _t, #"Event End" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event from", type datetime}, {"Event End", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if [Event from]-Table.SelectRows(#"Added Index", (x)=> x[Index]=([Index]-1))[Event End]{0}<#duration(0,1,0,0) then null else [Index] otherwise [Index]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"Count", each _, type table [Event from=nullable datetime, Event End=nullable datetime, Index=number, Custom=number]}})
in
    #"Grouped Rows"