Forum Discussion

InquisitiveOne's avatar
InquisitiveOne
New Member
4 years ago
Solved

Subtracting rows in power query editor

Hi I am trying to figure out what's the best way to subtract two rows in Power Query Editor:   I basically would like to do folllwing: If Ref = RB and ObjectID of RB = ObjectID of L1 = Obje...
General Comment
Need Help
Tutorial Requests
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    Assuming your Object ID's are unique and always in groups of three, then the code below may get you started.

    • Group by Object ID
    • Check to ensure that the Ref column includes RB
    • If it does, then do the subtraction
      • Don't need to check the ID are all the same since we grouped them, they must be
    • When we re-expand the table, we get the time diff in all the related rows, but that could be when you display the results, that can easily be changed if necessary

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Object ID", Int64.Type}, {"Ref", type text}, {"DateTime", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Object ID"}, {
        {"all", each _, type table [Object ID=nullable number, Ref=nullable text, DateTime=nullable datetime]},
        {"RB Time Diff", (t)=> 
            if List.Contains(t[Ref],"RB") 
                then t[DateTime]{List.PositionOf(t[Ref],"L1")} -t[DateTime]{List.PositionOf(t[Ref],"L2")}                
                else null, type duration}
        }),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Ref", "DateTime"}, {"Ref", "DateTime"})
in
    #"Expanded all"

     

     

     

InquisitiveOne's avatar
InquisitiveOne
New Member
4 years ago

They are always mean to be 3 records per set  RB, L1 and L2 or AB, L1 and L2...

There are some outliers but removing them too.

So your suggestion is to do a custom column to do grouping? Can you please elaborate what that function/column could look like?

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    Assuming your Object ID's are unique and always in groups of three, then the code below may get you started.

    • Group by Object ID
    • Check to ensure that the Ref column includes RB
    • If it does, then do the subtraction
      • Don't need to check the ID are all the same since we grouped them, they must be
    • When we re-expand the table, we get the time diff in all the related rows, but that could be when you display the results, that can easily be changed if necessary

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Object ID", Int64.Type}, {"Ref", type text}, {"DateTime", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Object ID"}, {
        {"all", each _, type table [Object ID=nullable number, Ref=nullable text, DateTime=nullable datetime]},
        {"RB Time Diff", (t)=> 
            if List.Contains(t[Ref],"RB") 
                then t[DateTime]{List.PositionOf(t[Ref],"L1")} -t[DateTime]{List.PositionOf(t[Ref],"L2")}                
                else null, type duration}
        }),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Ref", "DateTime"}, {"Ref", "DateTime"})
in
    #"Expanded all"