Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
TaraK
New Member

Calculate Difference Between Two Rows

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:

TaraK_0-1633012185059.png

 

Thanks in advance!

1 ACCEPTED SOLUTION
Jakinta
Solution Sage
Solution Sage

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

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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_0-1633400515782.png

 

 

ronrsnfld
Super User
Super User

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_0-1633400515782.png

 

 

Jakinta
Solution Sage
Solution Sage

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors