Power Query - Custom Column - Reference Previous Cell

[lager1001]

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?

[TABLE="width: 465"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD]Order[/TD]
[TD]Material[/TD]
[TD]Order QTY[/TD]
[TD]Inventory[/TD]
[TD]Available Inventory[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]12121212[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]12121212[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]12121212[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD]12121212[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]234567[/TD]
[TD]12121212[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD]2345678[/TD]
[TD]12121212[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]-6[/TD]
[/TR]
[TR]
[TD]465546546[/TD]
[TD]12121212[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]-7[/TD]
[/TR]
[TR]
[TD]23525663[/TD]
[TD]12121212[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]-8[/TD]
[/TR]
[TR]
[TD]96878768[/TD]
[TD]12121212[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]-9[/TD]
[/TR]
[TR]
[TD]12345678[/TD]
[TD]12341234[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]123456789[/TD]
[TD]12341234[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]234567989[/TD]
[TD]12341234[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]464646456[/TD]
[TD]12341234[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD]546456464[/TD]
[TD]12341234[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]-6[/TD]
[/TR]
[TR]
[TD]856745474[/TD]
[TD]12341234[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]-8[/TD]
[/TR]
</tbody>[/TABLE]

 

[Matt Allington]

This video I made a few years ago should help you. https://youtu.be/xN2IRXQ2CvI

 

[lager1001]

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]

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]

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

(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}),
    AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
    AddedRunningSum

2 -- Reference above function with a second query; assumes source data is in Table1 with row headers {Order, Material, ORDER QTY, Inventory}

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"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllData", "Available Inventory", each [Inventory]-[Running Sum]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Running Sum"})
in
    #"Removed Columns"

 

[lager1001]

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]

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]

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.