Distribute Product Stock Qty. Table to Multiple rows of Order Table

Question

I have Stock Table & I want to distribute the available stock qty to Order table where same products multiple orders available...Final result required as Result table in Power Query or Power Pivot...

Answer 1

If I understand what you are doing, here's one way to do this using Power Query M Code:
Algorithm is outlined in the code comments
I changed the data a bit to allow for more variations

let

//Read in the Stock and Order Tables
//Change these four lines as appropriate for your data sources
//NOTE: You can change data types for Order No and Product ID to "text" if that's what they really are
    Source = Excel.CurrentWorkbook(){[Name="StockTBL"]}[Content],
    Stock = Table.TransformColumnTypes(Source,{{"Product ID", Int64.Type}, {"Product Stock", Int64.Type}}),
    Source2 = Excel.CurrentWorkbook(){[Name="OrderTBL"]}[Content],
    Orders = Table.TransformColumnTypes(Source2,{
        {"Order No", Int64.Type}, {"Customer", type text}, {"Product ID", Int64.Type}, {"Order Qty", Int64.Type}}),

//Group Orders table by "Product ID"
// for each group, calculate the remaining stock quantity after each order
//    Running Total of all QTY's subtracted from the Product Stock quantity in the Stock table
    #"Grouped Rows" = Table.Group(Orders, {"Product ID"}, {
        {"Order RT", (t)=> 
            Table.FromColumns(
                Table.ToColumns(t) & {
                    List.Generate(
                        ()=>[rt=t[Order Qty]{0},
                        rm=List.Sum(Table.SelectRows(Stock, each [Product ID]=t[Product ID]{0})[Product Stock]) - t[Order Qty]{0}, 
                            idx=0],
                        each [idx] < Table.RowCount(t),
                        each [rt = [rt] + t[Order Qty]{[idx]+1},
                        rm=List.Sum(Table.SelectRows(Stock, each [Product ID]=t[Product ID]{0})[Product Stock]) - ([rt] + t[Order Qty]{[idx]+1}),
                                idx = [idx]+1],
                        each [rm])},{"Order No.", "Customer","Product ID","Order Qty","Remaining Stock"}),type table}
       }),

//Remove unneeded column and expand the grouped table
//then set data types
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Product ID"}),
    #"Expanded Order RT" = Table.ExpandTableColumn(#"Removed Columns", 
        "Order RT", 
        {"Order No.", "Customer", "Product ID", "Order Qty", "Remaining Stock"}, 
        {"Order No.", "Customer", "Product ID", "Order Qty", "Remaining Stock"}
        ),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Order RT",{{"Order No.", Int64.Type}, {"Customer", type text}, {"Product ID", Int64.Type}, {"Order Qty", Int64.Type}, {"Remaining Stock", Int64.Type}}),

//Add custom column to calculate Required Stock Qty depending on Order vs Remaining
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Required Stock Qty", each 
        if [Remaining Stock] >=0 then [Order Qty] 
        else List.Max({[Order Qty]+[Remaining Stock],0}), Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Remaining Stock"})
in
    #"Removed Columns1"