Inventory Repostion for Picking

[reynaldo_malave]

Hi guys,

I been stuck on this for a couple of days now. Using power query I am working on a picking line inventory restocking. The whole idea is to get products on the picking line on certain order.

This is my original table :

ProductoId	Available	RestockingNeed
11	150	160
11	1200	160
11	2500	160

This is what i am looking for: 

ProductoId	Available	Restocking	Restock
11	150	160	150
11	1200	10	10
11	2500	null

I am looking to get back the Restock column.

I any of you can help out here it would be great. 

I have been trying to adapt the solution

thanks.

Reynaldo

[AlienSx · Accepted solution]

hi, @reynaldo_malave try this

let
    Source = your_table,
    recs = List.Buffer(Table.ToRecords(Source)),
    count = List.Count(recs),
    g = 
        List.Generate(
            () => 
                [i = 0, 
                Productold = recs{0}[ProductoId], 
                Available = recs{0}[Available], 
                Restocking = recs{0}[RestockingNeed], 
                Restock = List.Min({Restocking, Available})],
            (x) => x[i] < count,
            (x) => 
                [i = x[i] + 1,
                Productold = recs{i}[ProductoId],
                Available = recs{i}[Available],
                Restocking = List.Max({0, x[Restocking] - x[Restock]}),
                Restock = List.Min({Available, Restocking})]
        ),
    z = Table.RemoveColumns(Table.FromRecords(g), {"i"})
in
    z

[reynaldo_malave]

Thanks @AlienSx  this worked like a charm 🙂