Running Stock Total based on forecasts and delivery's

[Stubrg]

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.

 

[ronrsnfld]

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"

[Stubrg]

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. 

[Stubrg]

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"

[ronrsnfld · Accepted solution]

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:

[Stubrg]

Thank you so much for this, I have been putting in countless late nights trying to find a solution, my understanding of coding in Power Query has grown massively over this time thanks to the patience of people like you.

[ronrsnfld]

OK, now I see that you added a column named "Part" to one of the tables. However, I don't see where you posted a table that included that column.

Again, please post as text which can by copy/pasted.

Also, the way to have the this running stock total by group, would be to group by Part first, and then use the code I provided in a custom aggregation within the Table.Group method.

[Stubrg]

Hi So here is the table with the added "Part" column. I thought i could just add the missing column into the code by doing the following:

So from a new Spreadsheet with this table in I added a new query, then using the original code I added in {"Part", type text},  into this line:

#"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}}),

and then edited this section adding in the part coulm again:

#"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])

PartForecast qty RT - MARStock TotalOn order (BF required date)J24 J24 Quantity=Int64.Type])

Thank you again for all your assistance.

PartForecast qty RT - MARStock TotalOn order (BF required date)J24 Quantity Screw 1 7 0 7 Screw 3 7 0 7 Screw 7 7 0 7 Bolt 7 7 0 7 Bolt 11 9 2 7 Bolt 13 9 2 7 Bolt 13 9 2 7 Clamp 6 4 0 4 Clamp 2 24 20 4 Clamp 8 31 7 4 Clamp 3 38 7 4 Fire Damper 10 30 0 30 Fire Damper 5 30 0 30 Fire Damper 3 60 30 30

[ronrsnfld]

The code I supplied with a "guess" for the Part column seems to work OK with your posted table.

Check it out.

[Stubrg]

Hi so ive been struggling on with this issue but seem to be making some progress Here is the routine in Advanced Editor, but all values returned are Null?

Any ideas, 

Thanks for your support, I feel like after a week of battling with this I am nearly there!

[Stubrg]

Hi Sorry to reach out again, but I really cant see what is wrong here? I have been attempting to understand each step but still getting the same error message??

[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})

[Stubrg]

Hi Daniel

Thank you for your coontinued patience, I now have the step running without errors but this is the result it has produced??

[wdx223_Daniel]

have no idea why you got this result.

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})

[Stubrg]

HiDaniel 

Please can you help? I have got the code running error free now but only get null values returned? I have been struggling with this and dont know where else to find a solution after many hours of trial and error and searching the internet. Attempting to run with Index columns etc. all to no success.

 

[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})

[Stubrg]

Hi thanks for your quick reply, I'm not sure what I'm missing? I am getting this error message below?

Many thanks

[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})

[Stubrg]

Hi noticed one obvious mistake with Typo, but still getting the same message?

[wdx223_Daniel]