Merge two rows based on certain conditions

[Anonymous]

I have a table of data in Power BI with several columns of interest "Event Frame Start Time", "Event Frame End Time", and "T-1002 Level Drop". I want to do the following: Whenever the time difference between the "Event Frame End Time" of one row and the "Event Frame Start Time" of the subsequent row is less than 1 hour, merge the two rows, i.e. Replace these two rows with one row containing the "Event Frame Start Time" value of the 1st row and the "Event Frame End Time" of the 2nd row, as well as having the sum of the "T-1002 Level Drop" values of the 2 rows as the "T-1002 Level Drop" value of the replaced row.  

I have tried to write code in a query to handle this but it was not successful. The context is that I have a dashboard that constantly receives new data so more rows are being added to this table. Hence, I need this query to be executed each time the table is updated with new rows, so that rows that need to be merged are merged. I would sincerely appreciate any help!

[Omid_Motamedise]

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"

[Anonymous]

Thank you for your help! 

I have managed to solve the problem myself. 

[Anonymous]

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.

[Anonymous · Accepted solution]

Thank you for your help! I have tried running your code, it takes relatively long to run, compared to mine. But yes, your code also works. Thanks!

I have written a code that more or less achieves what I want: 

let
    Source = R3010,
    #"FilteredRows1" = Table.SelectRows(Source, each [Event Frame End Time] <> null),
    #"Added Index" = Table.AddIndexColumn(#"FilteredRows1", "Index", 0, 1, Int64.Type),
    #"Added TimeDifference" = Table.AddColumn(#"Added Index", "TimeDifference", each if [Index] < Table.RowCount(#"Added Index") - 1 then 
    Duration.From(Table.Column(#"Added Index", "Event Frame Start Time"){[Index]+1} - [Event Frame End Time]) 
else 
    #duration(0,0,0,0)),
    #"AddCombineFlag" = Table.AddColumn(#"Added TimeDifference", "Combine Flag", each if [TimeDifference] = #duration(0,0,0,0) then "Last" 
    else if [TimeDifference] < #duration(0, 1, 0, 0) then "Combine" else "Keep"),
    #"CombinedRows1" = Table.AddColumn(#"AddCombineFlag", "CombinedLevelDrop", each 
    if [Combine Flag] = "Combine" then 
        [LevelDifference] + #"AddCombineFlag"[LevelDifference]{[Index] + 1} 
    else [LevelDifference]),
    #"CombinedRows2" = Table.AddColumn(#"CombinedRows1", "CorrectEndTime", each 
    if [Combine Flag] = "Combine" then 
        #"CombinedRows1"[Event Frame End Time]{[Index]+1}  
    else [Event Frame End Time]),
    #"Removed Columns" = Table.RemoveColumns(CombinedRows2,{"Event Frame End Time", "LevelDifference"}),
    #"AddPreviousCombineFlag" = Table.AddColumn(#"Removed Columns", "Previous Combine Flag", 
    each if [Index] > 0 then 
        #"Removed Columns"[Combine Flag]{[Index] - 1} 
    else null),
    #"FilteredRows" = Table.SelectRows(AddPreviousCombineFlag, each ([Previous Combine Flag] <> "Combine")),
    #"Reordered Columns" = Table.ReorderColumns(FilteredRows,{"Id", "30yv146 Open", "Event Frame Start Time", "Event Frame Duration", "Time", "PIBSA Grade", "R-3010", "PIIntTSTicks", "PIIntShapeID", "Index", "TimeDifference", "CombinedLevelDrop", "CorrectEndTime", "Combine Flag", "Previous Combine Flag"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"CombinedLevelDrop", "LevelDifference"}, {"CorrectEndTime", "Event Frame EndTime"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Combine Flag", "Previous Combine Flag"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Id", "30yv146 Open", "Event Frame Start Time", "Event Frame EndTime", "Event Frame Duration", "Time", "PIBSA Grade", "R-3010", "PIIntTSTicks", "PIIntShapeID", "Index", "TimeDifference", "LevelDifference"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Event Frame EndTime", "Event Frame End Time"}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns1",{"Id", "30yv146 Open", "Event Frame Start Time", "Event Frame End Time", "Event Frame Duration", "Time", "LevelDifference", "PIBSA Grade", "R-3010", "PIIntTSTicks", "PIIntShapeID", "Index", "TimeDifference"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns2",{{"LevelDifference", "R-3010 Level Increase"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns2",{"Index", "TimeDifference"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns2",{{"R-3010 Level Increase", type number}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type",{{"R-3010", "Reactor"}})
in
    #"Renamed Columns3"

[serpiva64]

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

[Anonymous]

For example, let's say we have these two rows: 

First column is the "Event Frame Start Time" and the 2nd column is "Event Frame End Time". The last column is "T-1002 Level Drop". The "Event Frame Start Time" of the 2nd row is less than hour after the "Event Frame End Time" of the 1st row. In this case, the desired code would merge these two rows:

We woud keep the entries in red rectangles. For the last column, the entry would be the sum of the two numbers. All the other columns are not relevant and can be deleted. 

[Omid_Motamedise]

post the data in type table here

[Anonymous]

The image below is a screenshot of the table containing the data of interest: