question

Martin-3024 avatar image
0 Votes"
Martin-3024 asked ImkeFeldmann-9931 edited

FIFO Inventory - Need help with List.Generate() or List.Accumulate()

Hi all,

I totally get the logic I need to apply here and would be able to code it in half an hour using python or whatever but I'm struggling putting it into PowerQuery.
I need to do the following, please see my data here:
82220-image.png



OpeningUnitRate is actually the column i need to calculate, it's just for better reading that it is already here in this case. It resembles the initial unit rate (which I got for some months).
I need:

UnitRate=
If OpeningUnitRate<>0 then
OpeningUnitRate
else
InventoryFromOpening(GroupIndex-1)**UnitRate*+ (I need the previous result of the column i'm calculating!)
InventoryFromPurchases(GroupIndex-1)*PurchasePrice(GroupIndex-1) (I have the PurchasePrice)
/
ClosingStock(GroupIndex-1) (I have the closingStock)


Thanks for your help!

power-query-not-supported
image.png (82.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ImkeFeldmann-9931 avatar image
0 Votes"
ImkeFeldmann-9931 answered ImkeFeldmann-9931 edited

Hello @Martin-3024 ,
you can use this function:

 // Index must start with 1
 (BufferedTable as table, IndexColumnName as text) =>
  List.Generate(
         () => BufferedTable{0} & [UnitRate = BufferedTable{0}[OpeningUnitRate], Continue = true], 
         each [Continue], 
         (Previous as any) =>
             let
                 Source = BufferedTable, 
                 #"Kept Range of Rows" = Table.Range(Source, Record.Field(Previous, IndexColumnName), 1), 
                 AddUnitRate = Table.AddColumn(
                     #"Kept Range of Rows", 
                     "UnitRate", 
                     each 
                         if [OpeningUnitRate] <> null then [OpeningUnitRate]
                         else
                             (
                                 BufferedTable[InventoryFromOpening]{Record.Field(Previous, IndexColumnName)}
                                     * Previous[UnitRate]
                                         + BufferedTable[InventoryFromPurchase]{Record.Field(Previous, IndexColumnName)}
                                     * BufferedTable[PurchasePrice]{Record.Field(Previous, IndexColumnName)}
                             )
                                 / BufferedTable[ClosingStock]{Record.Field(Previous, IndexColumnName)}
                 ),
                 ToRecord = AddUnitRate{0}, 
                 Continue = [Continue = Record.Field(Previous, IndexColumnName) + 1 <= Table.RowCount(Source)], 
                 Custom1 = try ToRecord & Continue otherwise Continue
             in
                 Custom1
     )


You can apply it to your table as a whole, but I don't recommend it.
Instead, you should group on the group where your indices belong to. Then feed those partitions to the function. This should run much faster than applying on the whole table.
But if you want to apply it to the whole table, you have to add an index column to the whole table (starting with 1) and pass that column name as the 2nd argument into the function.
Make sure to buffer the table inputs for performance reasons as well.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.