Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Subtracting Orders from Grouped Stock whilst adding in Stock Delivery's "Help Please!!"

Hi I am really struggling here, I am trying to create a new Column called "Running Stock" I can easily create this in Excel but Power Query is causing me a headache!

The calculation is needed by group and date order, The first calc subtracts the "Forecast qty" from the "Stock Total" the following calculation needs to subtract the "Forecast qty" from the first value of the "Running Stock" + any value in the "On order BF required date"

This then needs to reset at the next group.

I have tried to refer to the previous row using grouped index columns but cant work out how to start this calc after the first calc from a different column? and keeping it in date order?

Below is how I want the report to look:

 

I have tried to show with colour coding what cells i need to subrtact and add together.

Any assitance with this would be greatly apprecaited

Need Help

1 Reply

  • latimeria's avatar
    latimeria
    Icon for Solution Specialist rankSolution Specialist
    3 years ago

    Hi Anonymous ,

     

    You can try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdG9CsIwEAfwd8lcuK80TVYfwMWxdBI3QRDF1/cu1jZpCoUcyfDj+r/rODokQA+MLK5zl+vz9tGbtAYtzPfUKeMdJi3zgP2WDQ2jBBiOGQsQNtksXNLitR2DHnW+dNI4mzW7/sBJMcXpcX/pFbT8nM4vs4aNsh45Ba+OfJFudtG299/yj3FqmXWWWLHqf53fpsi+JTiH00d2EfRUrt9hul+imtkrLC3NTV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Due Date" = _t, Part = _t, #"Forecast qty" = _t, #"Stock Total" = _t, #"On order BF required date" = _t, #"Quantity in Store" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Forecast qty", Int64.Type}, {"Stock Total", Int64.Type}, {"On order BF required date", Int64.Type}, {"Quantity in Store", Int64.Type}}),
    #"Grouped Rows" = Table.Group(
        #"Changed Type", 
        {"Part"}, 
        {
            {"AllRows", each 
                let
                    ListForecast = List.Buffer([Forecast qty]),
                    ListSOrderBF = List.Buffer([On order BF required date]),
                    RunningStock = 
                        List.Generate(
                            ()=> [RT = [Stock Total]{0} - ListForecast{0} , Counter = 0 ], /* record first row */
                            each [Counter] < List.Count(ListForecast),
                            each [
                                RT = List.Sum({[RT], -ListForecast{[Counter]+1}, ListSOrderBF{[Counter]+1}}),
                                Counter = [Counter] +1
                            ],  /* record next row */
                            each [RT] /*select RT as result*/
                        )
                in /* transform table to columns, add new column and back to table */
                    Table.FromColumns(
                        Table.ToColumns(_) & { Value.ReplaceType(RunningStock, type {Int64.Type})},
                        Table.ColumnNames(_) & {"Running Stock"}
                    )
            }
        }
    ),
    Expanded = Table.Combine(#"Grouped Rows"[AllRows])
in
    Expanded

     

    Result:

    You can find explantion here Compute a Running Total by Category in Power Query - BI Gorilla

    and here Quickly Create Running Totals in Power Query • My Online Training Hub