Power Query - Custom Column - Reference Previous Cell

lager1001

New Member
Joined
May 17, 2019
Messages
19
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?

OrderMaterialOrder QTYInventoryAvailable Inventory
1234512121212176
12345612121212175
123456712121212471
2345612121212170
2345671212121227-2
23456781212121247-6
4655465461212121217-7
235256631212121217-8
968787681212121217-9
1234567812341234286
12345678912341234383
23456798912341234380
4646464561234123458-5
5464564641234123418-6
8567454741234123428-8

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

lager1001

New Member
Joined
May 17, 2019
Messages
19
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
Joined
May 17, 2019
Messages
19
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
Joined
Nov 2, 2017
Messages
81
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"
 

lager1001

New Member
Joined
May 17, 2019
Messages
19
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
Joined
May 17, 2019
Messages
19
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
Joined
May 17, 2019
Messages
19
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.
 

Forum statistics

Threads
1,077,910
Messages
5,337,108
Members
399,126
Latest member
MadhuK

Some videos you may like

This Week's Hot Topics

Top