Calculate Difference Between Two Rows

[TaraK]

Hi there,

I am trying to calculate the difference between rows, when the ID matches in column 1, and criteria in column 2 equal something specific, calculate the difference for column 3 between the rows and add that delta to a new column "4".

Here is what I have to work with:

Thanks in advance!

[Syndicate_Admin]

Not quite sure of all your details, but if 

• the Base category is the category listed in the first entry in the Category column and
• the real data is in pairs as you show then
• create a new "value" column that is the previous row relative to the original value column
• create the Delta column value when the Category column contains P+"baseCategory"

Note that using the Previous Row technique can be faster than using an Index column for large amounts of data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk/NyVEwVNJRCnAJAJKGBgZKsTrIwtoQCSNkCSOEelN0YZh6c1OEhDFIItQFSJpbGqAJa0MkLEAGxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Well ID" = _t, Category = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Well ID", type text}, {"Category", type text}, {"Value", Int64.Type}}),

    //base Category
    baseCategory = #"Changed Type"[Category]{0},

    //offset a row
    prevRow = {null} & List.RemoveLastN(#"Changed Type"[Value],1),
    newTbl = Table.FromColumns(
                Table.ToColumns(#"Changed Type") & {prevRow},
                Table.ColumnNames(#"Changed Type") & {"prevRow"}),

    //Add the delta column
    #"Added Custom" = Table.AddColumn(newTbl, "Delta", each if [Category] = "P+" & baseCategory then [Value]-[prevRow] else 0),

    //remove the prevRow column
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"prevRow"})
in
    #"Removed Columns"

[ronrsnfld]

Not quite sure of all your details, but if 

• the Base category is the category listed in the first entry in the Category column and
• the real data is in pairs as you show then
• create a new "value" column that is the previous row relative to the original value column
• create the Delta column value when the Category column contains P+"baseCategory"

Note that using the Previous Row technique can be faster than using an Index column for large amounts of data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk/NyVEwVNJRCnAJAJKGBgZKsTrIwtoQCSNkCSOEelN0YZh6c1OEhDFIItQFSJpbGqAJa0MkLEAGxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Well ID" = _t, Category = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Well ID", type text}, {"Category", type text}, {"Value", Int64.Type}}),

    //base Category
    baseCategory = #"Changed Type"[Category]{0},

    //offset a row
    prevRow = {null} & List.RemoveLastN(#"Changed Type"[Value],1),
    newTbl = Table.FromColumns(
                Table.ToColumns(#"Changed Type") & {prevRow},
                Table.ColumnNames(#"Changed Type") & {"prevRow"}),

    //Add the delta column
    #"Added Custom" = Table.AddColumn(newTbl, "Delta", each 
        if [Category] = "P+" & baseCategory 
        then [Value]-[prevRow] 
        else 0),

    //remove the prevRow column
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"prevRow"})
in
    #"Removed Columns"

[Jakinta · Accepted solution]

You can try this in blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk/NyVEwVNJRCnAJAJKGBgZKsTrIwtoQCSNkCSOEelN0YZh6c1OEhDFIItQFSJpbGqAJa0MkLEAGxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Well ID" = _t, Category = _t, Value = _t]),
    HelperCol = Table.AddColumn(Source, "Helper", each if not Text.Contains([Category],"+") then [Category] else Text.AfterDelimiter([Category], "+"), type text),
    Index1 = Table.AddIndexColumn(HelperCol, "Index", 0, 1, Int64.Type),
    Grouped = Table.Group(Index1, {"Well ID", "Helper"}, {{"A", each let t=_ in Table.AddColumn( Table.AddIndexColumn(t, "Index2",-1,1), "Delta", each   if [Helper]="PDP" then try Number.From([Value]) - Number.From(t[Value]{[Index2]}) otherwise 0 else 0  ), type table [Well ID=nullable text, Category=nullable text, Value=nullable text, Helper=text, Index=number, Delta=number]}}),
    Removed = Table.SelectColumns(Grouped,{"A"}),
    Expanded = Table.ExpandTableColumn(Removed, "A", List.Difference( Table.ColumnNames(Removed[A]{0}), {"Helper","Index2"} )),
    Sorted = Table.Sort(Expanded,{{"Index", Order.Ascending}}), //Safety step to keep initial order
    RemovedIndex = Table.RemoveColumns(Sorted,{"Index"})
in
    RemovedIndex

[TaraK]

Thanks for the reply! This got me almost there. Appreciate it!