Identify used batch index numbers AND split up and add records for each batch

[m_dekorte]

Hope this is possible.... I have two tables. Table 1 contains Material Receipts and Table 2 contains Material Demand.

I would need to determine for each Material Demand line which Material Receipt line was used and if more than one Material Receipt line was needed, split the Material Demand line up and show how much from which Material Receipt batch was used. 

I've been exploring List.Accumulate to see if I could identify the Material Receipts Index number(s) but I'm running into a cyclic reference error, I expects that's due to the fact that I have to reference the result for [RT] (= RunningTotal) to determine the action for the [Index]...  But even if I could overcome that I would need to take it several steps further because the cycles for [RT] and [Index] need to move independently from each other and I would also need to add new records... 

Here's my sample Material Reciepts table:

Date	KG	Source	Index
10-1-2020	20000	tReceipts	1
15-1-2020	176	tReceipts	2
3-3-2020	20000	tReceipts	3

And my sample Material Demand table:

Date	KG	Source	Source Index
14-2-2020	-171,575	tShipments	1
17-2-2020	-121,105	tStock	1
20-3-2020	-20187,977	tShipments	2
25-3-2020	-5046,977	tShipments	3

This would be the desired result, as you can see outlined Demand also needs to be split up for each used source batch.

Does anyone know how to achieve this with Power Query?

Thanks for any assistance you can provide and keep safe. 

[Stachu · Accepted solution]

I made it work, but there are some prerequisites - both tables need to have a unique index and Cumulative KG column, like this:

Function for getting the first receipt that has cumulative value bigger than cumulative demand. It has to be named 'giveReceipt'

giveReceipt

(value as number) => let
    Source = #"Material Receipts",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Index", "CumulativeR"}),
    FirstBigger = Table.SelectRows(#"Removed Other Columns", each [CumulativeR] >= value ),
    Max = List.Min(FirstBigger[Index])
in
    Max

Material Receipts

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQNdQ1MjAyUNJRMjIAAiBdEpSanJpZUFIMZBsqxeoAVZkiVBmam6GpMQKrMdY1xmeQsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, KG = _t, Source = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"KG", Int64.Type}, {"Source", type text}, {"Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CumulativeR", each List.Sum(List.Range(#"Changed Type"[KG],0,[Index])))
in
    #"Added Custom"

Material Demand

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxLCoAwDEXRvWTclCRtja7DYelIBEX8gN0/FgpSxNm7cHgxAnsUFBICA8jKNmgoM4/Leu3zke8SDMkUqa0UtkxV5nPaXiWE7lVC3KsdVL+PUm1obCDf/VEHKT0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, KG = _t, Source = _t, #"Source Index" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"KG", type number}, {"Source", type text}, {"Source Index", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    AddedCumulative = Table.AddColumn(#"Added Index", "Cumulative", each List.Sum(List.Range(#"Changed Type"[KG],0,[Index]+1))),
    #"Changed Type1" = Table.TransformColumnTypes(AddedCumulative,{{"Cumulative", type number}}),
    AddedFirstBigger = Table.AddColumn(#"Changed Type1", "Max", each giveReceipt(-[Cumulative])),
    Custom2 = Table.FromList( List.Combine({{1},List.RemoveLastN(AddedFirstBigger[Max],1)}), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Custom1 = Table.FromColumns(Table.ToColumns(AddedFirstBigger)&Table.ToColumns(Custom2), Table.ColumnNames(AddedFirstBigger)&{"Min"}),
    #"Added Custom" = Table.AddColumn(Custom1, "Receipt", each {[Min]..[Max]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Max", "Min"}),
    #"Expanded Receipt" = Table.ExpandListColumn(#"Removed Columns", "Receipt"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Receipt", {"Receipt"}, #"Material Receipts", {"Index"}, "Material Reciepts", JoinKind.LeftOuter),
    #"Expanded Material Reciepts" = Table.ExpandTableColumn(#"Merged Queries", "Material Reciepts", {"KG", "CumulativeR"}, {"Receipt.KG", "Receipt.CumulativeR"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Material Reciepts", "Custom", each [Cumulative]+[Receipt.CumulativeR])
in
    #"Added Custom1"

there is lot of dependencies between the tables so it may be good if you try to see it in a new file first. It's not giving the final state that you needed, but has all the details to get there:

e.g. Custom should replace negatives with 0, etc.

[Stachu]

I really don't get how do you merge the 2 tables. Is there additonal key in Material Receipt specyfing which entry from Material Demand it's relating to? Or are the receipts related to all records in the demand table, and there is some additional logic used that you didn't mention?

[m_dekorte]

No that's exactly the problem. 

There are no keys between the tables, everything needs to be calculated 100% FIFO.

[Stachu · Accepted solution]

I made it work, but there are some prerequisites - both tables need to have a unique index and Cumulative KG column, like this:

Function for getting the first receipt that has cumulative value bigger than cumulative demand. It has to be named 'giveReceipt'

giveReceipt

(value as number) => let
    Source = #"Material Receipts",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Index", "CumulativeR"}),
    FirstBigger = Table.SelectRows(#"Removed Other Columns", each [CumulativeR] >= value ),
    Max = List.Min(FirstBigger[Index])
in
    Max

Material Receipts

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQNdQ1MjAyUNJRMjIAAiBdEpSanJpZUFIMZBsqxeoAVZkiVBmam6GpMQKrMdY1xmeQsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, KG = _t, Source = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"KG", Int64.Type}, {"Source", type text}, {"Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CumulativeR", each List.Sum(List.Range(#"Changed Type"[KG],0,[Index])))
in
    #"Added Custom"

Material Demand

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxLCoAwDEXRvWTclCRtja7DYelIBEX8gN0/FgpSxNm7cHgxAnsUFBICA8jKNmgoM4/Leu3zke8SDMkUqa0UtkxV5nPaXiWE7lVC3KsdVL+PUm1obCDf/VEHKT0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, KG = _t, Source = _t, #"Source Index" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"KG", type number}, {"Source", type text}, {"Source Index", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    AddedCumulative = Table.AddColumn(#"Added Index", "Cumulative", each List.Sum(List.Range(#"Changed Type"[KG],0,[Index]+1))),
    #"Changed Type1" = Table.TransformColumnTypes(AddedCumulative,{{"Cumulative", type number}}),
    AddedFirstBigger = Table.AddColumn(#"Changed Type1", "Max", each giveReceipt(-[Cumulative])),
    Custom2 = Table.FromList( List.Combine({{1},List.RemoveLastN(AddedFirstBigger[Max],1)}), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Custom1 = Table.FromColumns(Table.ToColumns(AddedFirstBigger)&Table.ToColumns(Custom2), Table.ColumnNames(AddedFirstBigger)&{"Min"}),
    #"Added Custom" = Table.AddColumn(Custom1, "Receipt", each {[Min]..[Max]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Max", "Min"}),
    #"Expanded Receipt" = Table.ExpandListColumn(#"Removed Columns", "Receipt"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Receipt", {"Receipt"}, #"Material Receipts", {"Index"}, "Material Reciepts", JoinKind.LeftOuter),
    #"Expanded Material Reciepts" = Table.ExpandTableColumn(#"Merged Queries", "Material Reciepts", {"KG", "CumulativeR"}, {"Receipt.KG", "Receipt.CumulativeR"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Material Reciepts", "Custom", each [Cumulative]+[Receipt.CumulativeR])
in
    #"Added Custom1"

there is lot of dependencies between the tables so it may be good if you try to see it in a new file first. It's not giving the final state that you needed, but has all the details to get there:

e.g. Custom should replace negatives with 0, etc.

[m_dekorte]

@Stachu Thank you for your response. This looks very promising indeed!

I ran into an issue though. Like you suggested I copied your codes to blank queries in a new file. But the AddedFirstBigger step in the Material Demand query is only returning nulls and the Custom 1 step is retuning a 1 for the first- and nulls for all other records.  This is causing errors in subsequent steps.

I'm sorry to say that I haven't been able to fix that... would you be so kind to take another look?

Many thanks in advance!

[Stachu]

have you added my code to your queries, or you cannot replicate it in your setup? If you try to replicate - have you added the [CumulativeR] to Material Receipt? can you share your M code for Material Recepit?

[m_dekorte]

In my first attempt I had just copied all your code into blank queries in a new file.

Now I've tried to implement your code into my queries... and that worked.

Apologies and thank you again!

[artemus · Accepted solution]

Here is another solution

let
    processDemand = (batchIndex as number, recieptIndex as number, usedFromBatch as nullable number, remainingFromReciept as nullable number) =>
        let
            isNewBatch = newUsedFromBatch = 0,
            isNewReciept = newRemainingFromReciept = 0, 
            currentReciept = #"Material Reciepts"{recieptIndex}?,
            currentDemand = #"Material Demand"{batchIndex}?, 
            newUsedFromBatch = List.Min({remainingFromReciept + usedFromBatch, 0}),
            newRemainingFromReciept = List.Max({remainingFromReciept + usedFromBatch, 0})
        in
            if currentReciept = null or currentDemand = null then
                {}
            else
                {currentDemand & [Receipt Index = recieptIndex, Receipt KG = #"Material Reciepts"{recieptIndex}[KG], Used from Batch = usedFromBatch, Remaining from Batch = newRemainingFromReciept]} &
                (
                    let
                        batchIndex = if isNewBatch then batchIndex + 1 else batchIndex,
                        newUsedFromBatch = if isNewBatch then #"Material Demand"{batchIndex}?[KG]? else newUsedFromBatch,
                        recieptIndex = if isNewReciept then recieptIndex + 1 else recieptIndex,
                        newRemainingFromReciept = if isNewReciept then #"Material Reciepts"{recieptIndex}?[KG]? else newRemainingFromReciept
                    in
                        @processDemand(batchIndex, recieptIndex, newUsedFromBatch, newRemainingFromReciept)
                )
in
    Table.FromRecords(processDemand(0, 0, #"Material Demand"{0}[KG], #"Material Reciepts"{0}[KG]), type table [Date = date, KG = number, Source = text, Source Index = Int64.Type, Receipt Index = Int64.Type, Receipt KG = number, Used from Batch = number, Remaining from Batch = number])

[m_dekorte]

@artemus Thank you for your reply! 

I've tested it and your solution also works - amazing stuff.