Forum Discussion
Merge two rows based on certain conditions
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...
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"
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
OutputTableAnd 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.
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"