CODEWITHSUNDEEP
exceldaxpowerquery
Gustav Coetzee
Gustav Coetzee

Reputation: 59

power query subtract row above from row below

I am using power query in excel and i used create custom column to create a new column, what i desperately need is for this new column to take the value from the second row and subtract it from the first row , and again this will need to happen for all rows like so: row two is subtracted from row one, and row three will be subtracted from row two and row four will be subtracted from row three. PLEASE help. I have no understanding of dax nor power query started using it today and i only need this one thing to work

PS. I have an index that starts from one, called index here is the code

= Table.AddColumn(#"Reordered Columns", "Custom", each [#"ODO - Km"] - [#"ODO - Km"])

At this moment the ODO km is subtracting from the ODO km in the same row, I want the previous odo km to subtract from the next ODO km.

Upvotes: 1

Views: 11862

Answers (3)

Werapon K.
Werapon K.

Reputation: 1

I have found my own solution after trying to solve the same problem! The add-in tool has been there in the program, no need for manual coding. You can trasnpose the whole table such that rows and columns get switched. Then, promote first row as headers and add calculated column with Subtract tools. Delete other unused columns and demote headers, then transpose back. Now, you get the new row with subtracted values from 2 original rows!!

Upvotes: 0

Stefan
Stefan

Reputation: 31

You don't need to do this. You can index rows in a table by using an index. The key is to reference the name of the previous step like below:

let
    Source = whatever
    addindex = Table.AddIndexColumn(Source , "Index", 0, 1),
    addRelative = Table.AddColumn(addindex, "Previous record", each try if [Index]<>0 then addindex[myField]{[Index]-1}),
in
   addRelative

Upvotes: 3

greggyb
greggyb

Reputation: 3798

Create two indexes, one 0-based, called Index0, and one 1-based, called Index1. Merge the query with itself joining on Index1 = Index0. You'll now have duplicate of every column, but they will be offset by one. Then you can do all of your arithmetic in one row. After this, you can remove all but the result fields you want.

Upvotes: 5

Related Questions