Forum Discussion
Running Stock Total based on forecasts and delivery's
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:
Super UserI 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.
Hi Thank you again for your quick response, appoligies for the screenshots below is code copy/pasted, I have added an extra column in the original spreedsheet and created a new query which i have copy/pasted the code you kindly provided.
I have then attempted to edit and add in the additonal column name, the idea is to have the Stock "Running Total" reset at each Part by using the grouping function in Power Query.
Thank you again for your continued support, i appreciate it.
let
//Change next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[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}}),
//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(
{#"Changed Type"[Part]}
& {fq}
& {#"rtList"}
& {#"Changed Type"[Stock Total]}
& {oo}
& {#"Changed Type"[J24 Quantity]},
type table[#"Part", type 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])
in
#"Result"