# Power Query - Custom Column - Reference Previous Cell

#### lager1001

##### New Member
I use a formula in my excel workbook that references the previous cell or cell above in order to calculate inventory based on order count. I have searched far and wide for a way to do this in Power Query in a custom column and have had no luck. Can anyone assist? I would like this in a custom column in Power Query. I need this a custom column formula (the formula one would enter in the custom column box, not in the advanced editor).

It subtracts the order quantity from the available inventory based on the order quantity of the line above it. The formula I use in excel is:

=IF(B2-B1,E1-C2,D2-C2) Why does Power Query make these simple formulas so difficult?

 Order Material Order QTY Inventory Available Inventory 12345 12121212 1 7 6 123456 12121212 1 7 5 1234567 12121212 4 7 1 23456 12121212 1 7 0 234567 12121212 2 7 -2 2345678 12121212 4 7 -6 465546546 12121212 1 7 -7 23525663 12121212 1 7 -8 96878768 12121212 1 7 -9 12345678 12341234 2 8 6 123456789 12341234 3 8 3 234567989 12341234 3 8 0 464646456 12341234 5 8 -5 546456464 12341234 1 8 -6 856745474 12341234 2 8 -8

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### lager1001

##### New Member
Thank you very much for your reply. I had tried playing around with this approach before and for whatever reason it doesn't seem to get the math right. I will continue to tinker.

#### lager1001

##### New Member
I'm unable to figure this out. I have created 2 index columns, one starting at 0 and one starting at 1 and merged the table onto itself. It works fine for the first 2 item numbers, if they are the same, but not for any more than that. I cannot wrap my mind around how to make it work. What would the custom column syntax be for this?

#### horseyride

##### Board Regular
If I had to do this, I'd create a function to do cumulative sum on Order Qty, and then reference it with a group and custom column. Code below

1 -- create function and name it fnRunningSum

Code:
``````(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Accumulate(Source[Order QTY],{},(cumulative,amount) => cumulative & {List.Last(cumulative, 0) + amount}),
in
2 -- Reference above function with a second query; assumes source data is in Table1 with row headers {Order, Material, ORDER QTY, Inventory}

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Material"}, {{"AllData", fnRunningSum}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Order", "Order QTY", "Inventory", "Running Sum"}, {"Order", "Order QTY", "Inventory", "Running Sum"}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Running Sum"})
in
#"Removed Columns"``````

#### lager1001

##### New Member
I will give this approach a try and see if it slows down my report. Indexing slowed it down way to much that I just ended up loading the table and adding a calculated column in the spreadsheet itself on the end. I don't like the layout but I can live with it.

#### lager1001

##### New Member
I'm still unable to find a solution in Power Query to this problem. Does anyone know of a simple way to achieve this?

#### lager1001

##### New Member
I was finally able to find a FAST solution that doesn't slow down the query when it is refreshed the way the indexing method was. I found it at this YouTube link if anyone is encountering the same issue.