PowerQuiery to Allocate numbers if stock is available.

[Pavan.Sada.PS]

Hello Fellas,

Need your help and assistance here. I am looking for powerquiery solution as i have data around 10 lakh plus rows to do the calculation for.

To explain about the data: on a daily basis we get the customer orders and we check if the stock is available we allocate them to those customers (we follow FIFO basis ) and bill them accordingly.

In the attached file column A is order received date for the customer in b column for product (in C column) with grid size (in d column) for qty (E column)

Column F is the stock available in that grid wise stock. i need to update G column on the qty which can be allocated to each of the customers on fifo basis.


Regards,
Pavan S

 

[Chihiro]

Can you add what's the expected outcome for current data? Formula or calculation steps explained with manual entry.

 

[Pavan.Sada.PS]

Can you add what's the expected outcome for current data? Formula or calculation steps explained with manual entry.

Chihiro,

I have explained the manual steps which i used to follow and expected answers are highlighted in yellow.

 

[Chihiro]

Excel isn't ideal for this sort of operation. If possible, leverage Access or some other Database to manage this.

See attached for what can be done via PowerQuery .

It can probably be simplified a bit. But will do what you are looking for.

Complete M for reference:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Customer No", type text}, {"ProductCode", type text}, {"ProductSize", type text}, {"Open Qty", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Customer No", "ProductCode", "ProductSize", "Open Qty"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Index", Int64.Type}}),
// Custom SUMIFS
    mySumIfsFunc = (InputTable as table, myIndex as number, myDate as date, myProdCode as text, myProdSize as text) as number =>
        let
            FilteredTable = Table.SelectRows(InputTable, each ([Index] < myIndex and [Date] < myDate and [ProductCode] = myProdCode and [ProductSize] = myProdSize)),
            SumOfQty = List.Sum(Table.Column(FilteredTable, "Open Qty"))
        in
            SumOfQty,
// End of Custom SUMIFS
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "SUMIFS", each mySumIfsFunc(#"Changed Type1", [Index], [Date], [ProductCode], [ProductSize])),
    #"Added Custom1" = Table.TransformColumnTypes(Table.AddColumn(#"Added Custom", "SumPrevQty", each try Number.From([SUMIFS]) otherwise 0), {{"SumPrevQty", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"SUMIFS"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"ProductCode", "ProductSize"},Table3,{"ProductCode", "ProductSize"},"NewColumn",JoinKind.FullOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Available Stock"}, {"Available Stock"}),
    #"Sorted Rows" = Table.Sort(#"Expanded NewColumn1",{{"Index", Order.Ascending}}),
    #"Added Custom2" = Table.AddColumn(#"Sorted Rows", "Filled Qty", each if [SumPrevQty] = 0 and [Open Qty] < [Available Stock] then [Open Qty] else if [SumPrevQty] > 0 and ([SumPrevQty] + [Open Qty]) <= [Available Stock] then [Open Qty] else if [SumPrevQty] = 0 and [Open Qty] > [Available Stock] then [Available Stock] else if [SumPrevQty] < [Available Stock] and ([SumPrevQty] + [Open Qty]) > [Available Stock] then [Available Stock] - [SumPrevQty] else 0),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"SumPrevQty"})
in
    #"Removed Columns1"

NOTE: When customizing "M" functions in Advanced Editor, do note that it's case sensitive. Very typical cause of an error is case mismatch.