Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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"

     

     

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

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's avatar
Anonymous
Not applicable
1 year ago

Thank you for your help! 

 

I have managed to solve the problem myself.