Forum Discussion
Running Stock Total based on forecasts and delivery's
Hi
I am really struggling with this query as I am new to Power Query, I need to have a "Running Stock" column that when the report is first run the top line "Forecast qty RT - MAR" is subtracted from the "Stock Total" and returned in the "Running Stock" column each subsequent row in "Forecast qty RT - MAR" is subtracted from the previous rows "Running Stock" but if stock "On order(Bf required date)" changes i.e from 0 to 2 this value needs to be added into the "Running Stock" at first instance.
Below is what its looking like at the moment but I am only able to subtract the "Forecast qty RT - MAR" from the "Stock Total"
Here is how I would like it look, I dont know if i should be using Buffers??
so initally I subtract 1 from the 7 giving me 6 in the "Running Stock" I then subtract 3 from 6 giving me 3 in "Running Stock" and repeating until the first instance of 0 changing to 2 in "On order (Bf required date)" this stock is then added into the "Running Stock" column so -11 subtract -11 add 2 results in -20 the subtraction contiunes until a change in value in "On order (Bf required date)" again and the first instances is again added into the "Running Stock"
Any help that can be provided will be greatly appreciated as this is causing me many hours of trial and error with no success.
By the way, I see now that your error message is due to the fact that you added the Part column to the table type specification incorrectly. The columns should be in the form: [...Column Name = type...]. You show [...#"Part", type text...] which is adding two columns, one with the name "Part", and the second named "type text". See my code below for proper method.
Below is code that should work to give you a running stock qty per part number.
Here is the table with the Part column added:
Code:
let //Change next line to reflect actual data source Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Forecast qty RT - MAR", Int64.Type}, {"Stock Total", Int64.Type}, {"On order (Bf required date)", Int64.Type}, {"J24 Quantity", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Part"}, { {"Tables", (t)=> let //lists to compute running stock total fq = t[#"Forecast qty RT - MAR"], oo = t[#"On order (Bf required date)"], #"rtList" = List.Generate( ()=>[rt=t[Stock Total]{0} - fq{0} + oo{0}, idx=0], each [idx] < List.Count(fq), each [rt= if oo{[idx]} <> oo{[idx]+1} then [rt] - fq{[idx]+1} + oo{[idx]+1} else [rt] - fq{[idx]+1}, idx=[idx]+1], each [rt]), //combine running total column with original table #"Result" = Table.FromColumns( {t[Part]} & {fq} & {#"rtList"} & {t[Stock Total]} & {oo} & {t[J24 Quantity]}, { "Part", "Forecast qty RT - MAR", "Running Stock", "Stock Total", "On order (Bf required date)", "J24 Quantity"}) in #"Result", type table[ #"Part" = text, #"Forecast qty RT - MAR"=Int64.Type, Running Stock=Int64.Type, Stock Total=Int64.Type, #"On order (Bf required date)"=Int64.Type, J24 Quantity=Int64.Type] }}), #"Expanded Tables" = Table.ExpandTableColumn(#"Grouped Rows", "Tables", {"Forecast qty RT - MAR", "Running Stock", "Stock Total", "On order (Bf required date)", "J24 Quantity"}) in #"Expanded Tables"Results:
20 Replies
ronrsnfldSuper User
I suggest using the List.Generate function to create a running total List based on the criteria you mentioned.
Then recombine into a new table.
You 'll need to change the second line of code to reflect your actual data source (mine is in an Excel table and does NOT include the Running Stock total column). The rest should generate the result table you show as desired:
let //Change next line to reflect actual data source Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Forecast qty RT - MAR", Int64.Type}, {"Stock Total", Int64.Type}, {"On order (Bf required date)", Int64.Type}, {"J24 Quantity", Int64.Type}}), //lists to compute running stock total fq = #"Changed Type"[#"Forecast qty RT - MAR"], oo = #"Changed Type"[#"On order (Bf required date)"], #"rtList" = List.Generate( ()=>[rt=#"Changed Type"[Stock Total]{0} - fq{0} + oo{0}, idx=0], each [idx] < List.Count(fq), each [rt= if oo{[idx]} <> oo{[idx]+1} then [rt] - fq{[idx]+1} + oo{[idx]+1} else [rt] - fq{[idx]+1}, idx=[idx]+1], each [rt]), //combine running total column with original table #"Result" = Table.FromColumns( {fq} & {#"rtList"} & {#"Changed Type"[Stock Total]} & {oo} & {#"Changed Type"[J24 Quantity]}, type table[#"Forecast qty RT - MAR"=Int64.Type, Running Stock=Int64.Type, Stock Total=Int64.Type, #"On order (Bf required date)"=Int64.Type, J24 Quantity=Int64.Type]) in #"Result"Hi ronrsfld,
Thank you so much for your reply, it really has been causing me a headache trying to work out how to make this work. Your solution works amazing and I think I can understand each step, which is great for me being new to Power Query.
I am now attempting to use this code to build into a bigger report query. I have 2 extra columns added in the excel spreedsheet and trying to add these into the code you provided. But i get the following message?
Here is a the altered code:
I eventually want to be able to group this Running Total by group and need to add these extra columns in.
- ronrsnfld
Makes no sense to me, either. What you've posted as screenshots only shows seven column names, not nine. The only difference I see between the code I provided and what you posted is a change in the table reference. You'll need to provide more information. By the way, it is preferable to post code and data as text which can be copy/pasted, and not as screenshots which have to be laboriously re-typed.
- wdx223_Daniel
Community Champion
NewStep=Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{},(x,y)=>{if x={} then {y} else {x{0}&{y&[Running Stock=x{1}-y[#"Forcast qty RT - MAR"]],y[Running Stock]}){0})
- wdx223_Daniel
NewStep=Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{},(x,y)=>{if x={} then {y} else {x{0}&{y&[Running Stock=x{1}-y[#"Forcast qty RT - MAR"]]},y[Running Stock]}){0})
- wdx223_Daniel
NewStep= Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{},(x,y)=>{if x={} then {y} else x{0}&{y&[Running Stock=x{1}-y[#"Forcast qty RT - MAR"]]},y[Running Stock]}){0})
