Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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])
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?
No that's exactly the problem.
There are no keys between the tables, everything needs to be calculated 100% FIFO.
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 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!
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?
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!
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])
@artemus Thank you for your reply!
I've tested it and your solution also works - amazing stuff.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.