Transform Series of Relative Values to Absolute Values

[markus_e]

Hello together,

I am searching for help in transforming my D365 transaction logis into a line chart showing the stock value at a certain point in time (and this for all my products so that I can do further analysis then). Unfortunately from the transactions I only the the relative change (sold X items, purchased Y items).  But - what I have is the current amount on strock from which I could calculate backward the absolute value which I can then use in a line chart. So thats my rough concept I wanted to follow. 😉

From D365 I get a list with at least the following columns:

- Item Number (identification of the product)

- Physical Date

- Quantity

I already tried some concepts, but the one that worked at least form only a single Item Number was: 

- Filter for the transactions of one item number

- Add an index column so that I can reference the rows

- Merge the query with another query providing the currenty On-Hand value (so, if I am processing the rows later on for the row with Index 0 the Total value is the current On-Hand value and...

- Using "List.FirstN" I can then sum up all the changes until a given index (ok, could become a performance issue) and get the Total value for the other rows.

The final Power BI code then looks as followes:

let
    Source = Excel.Workbook(File.Contents("C:\Users\mareck00\OneDrive - OMICRON electronics GmbH\Subprocess Manager\Team\Purchase\Stock Trend\B0888001 EtherCat Cable 15m.xlsx"), null, true),
    AxTable1_Table = Source{[Item="AxTable1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(AxTable1_Table,{{"Item number", type text}, {"Physical date", type date}, {"Financial date", type date}, {"Quantity", Int64.Type}, {"Column1", Int64.Type}, {"Column2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column1"}),
    AddInd = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Merged Queries" = Table.NestedJoin(AddInd, {"Item number"}, OnHandByInventoryStatus, {"Item number"}, "OnHandByInventoryStatus", JoinKind.LeftOuter),
    #"Expanded OnHandByInventoryStatus" = Table.ExpandTableColumn(#"Merged Queries", "OnHandByInventoryStatus", {"Total"}, {"Total"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded OnHandByInventoryStatus",{{"Total", "Stock"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Total", each if ([Index] <> 0) then [Stock]-List.Sum(List.FirstN(Table.Column(#"Renamed Columns", "Quantity" ), [Index])) else [Stock])
in
    #"Added Custom"

This did work, but only for a single item number but as usual ERP has more than one item stored. 😯

The next step then was to make this possible for all Item numbers. In theory, I wanted to group all my transaction data by Item Number. For every group having all the original rows in a table, I wanted to apply the transformation steps from above on these tables: 

let
    Source = #"AllSalesTransactions (2)",
    #"Appended Query" = Table.Combine({Source, AllPurchaseTransactions, AllProductionTransactions}),
    #"Filtered Rows" = Table.SelectRows(#"Appended Query", each [Item number] = "P0005233"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Financial date", "Reference", "Number", "Receipt", "Issue", "Unit", "Cost amount", "Device number", "Version", "Warehouse", "Location", "Inventory status", "License plate", "Device reference"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Item number"}, OnHandByInventoryStatus, {"Item number"}, "OnHandByInventoryStatus", JoinKind.LeftOuter),
    #"Expanded OnHandByInventoryStatus" = Table.ExpandTableColumn(#"Merged Queries", "OnHandByInventoryStatus", {"Total"}, {"Stock"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded OnHandByInventoryStatus",null,0,Replacer.ReplaceValue,{"Stock"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Item number"}, {{"AllRows", each _, type table [Item number=nullable text, Physical date=nullable date, Quantity=nullable number, Total=nullable number]}}),
    AddInd2 = Table.TransformColumns(#"Grouped Rows", {"AllRows", each Table.AddIndexColumn(_, "Index", 0, 1)}),
    AddInd3 = Table.TransformColumns(AddInd2, {"AllRows", each Table.AddColumn(_, "Total", each List.Sum(List.FirstN(Table.Column(_, "Quantity" ), [Index])))})
in
    AddInd3

The error I get then is "Expression.Error: We cannot convert a value of type Record to type Table.". This is where I am stinding now 😕 Maybe I am already stuck too much into my direction or I am only a small step away. Or someone else has a better idea to do that. Thank you all for you help!!!

BR Markus

[markus_e]

Thanks, this worked!! BR Markus

[spinfuzer · Accepted solution]

You have each twice in your last step which is confusing Power Query.  Your Inner Function will replace the outer variable if each is used again.  When you use each inside of the Table.AddColumn, it turns the _ into the records so Table.AddColumn(record reference, ...) breaks the code.  Change one of your function variables like below

Change the each in Transform Columns

AddInd3 = Table.TransformColumns(AddInd2, {"AllRows", each Table.AddColumn(_, "Total", each List.Sum(List.FirstN(Table.Column(_, "Quantity" ), [Index])))})

to (x) =>

AddInd3 = Table.TransformColumns(AddInd2, {"AllRows", (x) => Table.AddColumn(x, "Total", each List.Sum(List.FirstN(Table.Column(x, "Quantity" ), [Index])))})

Now x will refer to your nested table and _ will refer to the rows of your nested table. 

Since you are using each in the Table.AddColumn  _[Index] is the same as [Index] (the index column of the current record/row in the table). 

If for some reason you used x[Index], that would refer to the entire Index column of the Table x.