Difference between two line entries with the same ID

[JohannesBier]

I would like to add a custom column that calculates the difference between two rows. I have a column ValueMax and would like to have a ValueColumn that calculates the difference to the previous ValueMax. Is this possible in PowerQuery?

Thank you!

Best regards

Johannes

[AlienSx · Accepted solution]

let
    s = your_table,
    rows = List.Buffer(Table.ToRecords(s)),
    add_col = List.Generate(
        () => [i = 0, val = rows{0} & [ValueColumn = 0]],
        (x) => rows{x[i]}? <> null,
        (x) => [i = x[i] + 1, val = rows{i} & [ValueColumn = rows{i}[ValueMax] - x[val][ValueMax]]],
        (x) => x[val]
    ),
    z = Table.FromRecords(add_col)
in
    z

[JohannesBier]

Can you explain it here?

[Anonymous · Accepted solution]

Hi @JohannesBier 

You can refer to the following solution.

Sample data

First step

Add index column group by the id and then expand it., you can refer to the following code or the video.

Insert a new step

= Table.Group(#"Changed Type"(the last step name), {"ID"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1),type table}})

Create Row Number for Each Group in Power BI using Power Query - RADACAD

Second Step:Add a custom column to calculate the difference

=[ValueMax]-List.Min(Table.SelectRows(#"Expanded Data"(last step name),(x)=>x[ID]=[ID] and x[Index]=[Index]-1)[ValueMax])

Output

And you can put the following code to advanved editor in power query to refer to 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrVgTCNEExjBNMEwjQCMk0RTCRRMwTTHMG0ADJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ValueMax = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ValueMax", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"ValueMax", "Index"}, {"ValueMax", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Difference", each [ValueMax]-List.Min(Table.SelectRows(#"Expanded Data",(x)=>x[ID]=[ID] and x[Index]=[Index]-1)[ValueMax]))
in
    #"Added Custom"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlienSx]

@JohannesBier you don't need to add custom column. My code modifies your initial table in to the one with new column as per your instructions. Please watch this video to know how to incorporate this code. 

[AlienSx · Accepted solution]

let
    s = your_table,
    rows = List.Buffer(Table.ToRecords(s)),
    add_col = List.Generate(
        () => [i = 0, val = rows{0} & [ValueColumn = 0]],
        (x) => rows{x[i]}? <> null,
        (x) => [i = x[i] + 1, val = rows{i} & [ValueColumn = rows{i}[ValueMax] - x[val][ValueMax]]],
        (x) => x[val]
    ),
    z = Table.FromRecords(add_col)
in
    z