Power Query - Custom Column - Reference Previous Cell

lager1001

Board Regular
Joined
May 17, 2019
Messages
88
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]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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?
 
Upvote 0
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}),
    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}

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"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllData", "Available Inventory", each [Inventory]-[Running Sum]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Running Sum"})
in
    #"Removed Columns"
 
Upvote 0
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.
 
Upvote 0
I'm still unable to find a solution in Power Query to this problem. Does anyone know of a simple way to achieve this?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,220
Messages
6,177,214
Members
452,764
Latest member
Mark1963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top