Forum Discussion

markus_e's avatar
markus_e
Regular Visitor
2 years ago
Solved

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 ...
  • spinfuzer's avatar
    2 years ago

    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.