power query subtract row above from row below

Question

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.

score 5 · Accepted Answer · answered Jan 22 '16 at 14:23

5

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.

answered Jan 22 '16 at 14:23

greggyb

3,728
1
11
32

Adding to this answer. You might also want to consider creating a new query that references the first so that you can then do a merge on Query1 with Query2 on the index after you have completed your arithmetic. This should keep your current query clean. – Jay Killeen Jan 24 '16 at 23:07

score 3 · Answer 2 · edited Jan 20 '17 at 03:55

3

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

edited Jan 20 '17 at 03:55

Tot Zam

8,406
10
51
76

answered Jan 20 '17 at 02:50

Stefan

31
3

I tried this and got the error: `Expression.SyntaxError: Token Else expected.` – SCool Feb 07 '22 at 10:45

score 0 · Answer 3 · answered Apr 22 '23 at 02:23

0

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!!

answered Apr 22 '23 at 02:23

Werapon K.

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 26 '23 at 14:24

power query subtract row above from row below

3 Answers3

Linked