Power Query - Custom Column - Reference Previous Cell

lager1001

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

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

lager1001

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

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

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,101,786
Messages
5,482,923
Members
407,368
Latest member
FunkyFriedChicken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top