Subtracting rows in power query editor

[InquisitiveOne]

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 = Object ID of L2 then DateTime of L2 - DateTime of L1

I am trying to pick a correct path because I would need to also do something similar for RA scenario:

 If Ref = RA and ObjectID of RA = ObjectID of L1 = Object ID of L2 then DateTime of L1 - DateTime of L2

[InquisitiveOne]

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 · Accepted solution]

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"

[ronrsnfld]

Need to know how to pick the relevant L1 and L2 for the given RB.   If there will never be more than three identical Object ID's, you could group by Object ID's.  Then check if you have an RB, L1 and L2 in each group and do your subtraction.  If the refs should be broken up into groups of three, then you would create a custom column to do the grouping.  If there is some other method to determine which L1 and L2 relate to a given RB, then you need to spell it out.

Once you decide how to determine which refs go together, you can write a custom aggregation to include the appropriate logic and output.