Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
markus_e
Regular Visitor

Transform Series of Relative Values to Absolute Values

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

 

 

1 ACCEPTED SOLUTION
spinfuzer
Super User
Super User

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.

View solution in original post

2 REPLIES 2
markus_e
Regular Visitor

Thanks, this worked!! BR Markus

spinfuzer
Super User
Super User

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors