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.
Hi everyone, Today I need your help!
I have this problem:
Demand forecast like below:
SKU | DESCRIPTION | PRODUCTION PLAN | UM | MONTH |
200 | CAKE | 10 | tons | mar/21 |
200 | CAKE | 20 | tons | abr/21 |
200 | CAKE | 30 | tons | mai/21 |
Two Bill Of Material
First level
SKU | DESCRIPTION |
| COMPONENTS | COMPONENTS DESCRIPTION | QTY PER tons | UM |
200 | CAKE |
| 600339 | PACK | 0,5 | KG |
200 | CAKE |
| 600340 | BOX | 2 | UNIT |
200 | CAKE |
| 600341 | ETIQ | 1 | UNIT |
200 | CAKE |
| 700100* | SEMI CAKE | 1000 | KG |
Second level
SKU | DESCRIPTION | COMPONENTS | COMPONENTS DESCRIPTION | QTY PER BOM | UM |
700100* | SEMI CAKE | 500001 | SUGAR | 500 | KG |
700100* | SEMI CAKE | 500002 | FOSF | 200 | KG |
700100* | SEMI CAKE | 500001 | MILK | 300 | KG |
And I would wish this output:
COMPONENTS | COMPONENTS DESCRIPTION | UM | QTY | MONTH |
600339 | PACK | KG | 5 | mar/21 |
600340 | BOX | UNIT | 20 | mar/21 |
600341 | ETIQ | UNIT | 10 | mar/21 |
700100 | SEMI CAKE | KG | 10000 | mar/21 |
500001 | SUGAR | KG | 5000 | mar/21 |
500002 | FOSF | KG | 2000 | mar/21 |
500001 | MILK | KG | 3000 | mar/21 |
600339 | PACK | KG | 10 | abr/21 |
600340 | BOX | UNIT | 40 | abr/21 |
600341 | ETIQ | UNIT | 20 | abr/21 |
700100 | SEMI CAKE | KG | 20000 | abr/21 |
500001 | SUGAR | KG | 10000 | abr/21 |
500002 | FOSF | KG | 4000 | abr/21 |
500001 | MILK | KG | 6000 | abr/21 |
600339 | PACK | KG | 15 | mai/21 |
600340 | BOX | UNIT | 60 | mai/21 |
600341 | ETIQ | UNIT | 30 | mai/21 |
700100 | SEMI CAKE | KG | 30000 | mai/21 |
500001 | SUGAR | KG | 15000 | mai/21 |
500002 | FOSF | KG | 6000 | mai/21 |
500001 | MILK | KG | 9000 | mai/21 |
Is it possible by measures?
Thank you in advanced
Solved! Go to Solution.
Hi @William_Moreno ,
looks like you need 2 merges for it.
let
Source = DemandForecast,
#"Merged Queries" = Table.NestedJoin(Source, {"DESCRIPTION", "SKU"}, FirstLevel, {"DESCRIPTION", "SKU"}, "FirstLevel", JoinKind.LeftOuter),
#"Expanded FirstLevel" = Table.ExpandTableColumn(#"Merged Queries", "FirstLevel", {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER tons", "UM"}, {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER tons", "UM.1"}),
#"Inserted Multiplication" = Table.AddColumn(#"Expanded FirstLevel", "Multiplication", each [QTY PER tons] * [PRODUCTION PLAN], Int64.Type),
Level1 = Table.RenameColumns(#"Inserted Multiplication",{{"Multiplication", "Amount"}}),
#"Merged Queries1" = Table.NestedJoin(Level1, {"COMPONENTS"}, SecondLevel, {"SKU"}, "SecondLevel", JoinKind.Inner),
#"Removed Other Columns" = Table.SelectColumns(#"Merged Queries1",{"PRODUCTION PLAN", "MONTH", "SecondLevel"}),
#"Expanded SecondLevel" = Table.ExpandTableColumn(#"Removed Other Columns", "SecondLevel", {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER BOM", "UM"}, {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER BOM", "UM.1"}),
Level2 = Table.AddColumn(#"Expanded SecondLevel", "Amount", each [PRODUCTION PLAN] * [QTY PER BOM], Int64.Type),
Custom1 = Level1 & Level2,
#"Removed Other Columns1" = Table.SelectColumns(Custom1,{"COMPONENTS", "COMPONENTS DESCRIPTION", "UM.1", "Amount", "MONTH"})
in
#"Removed Other Columns1"
Please check enclosed file.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @William_Moreno ,
looks like you need 2 merges for it.
let
Source = DemandForecast,
#"Merged Queries" = Table.NestedJoin(Source, {"DESCRIPTION", "SKU"}, FirstLevel, {"DESCRIPTION", "SKU"}, "FirstLevel", JoinKind.LeftOuter),
#"Expanded FirstLevel" = Table.ExpandTableColumn(#"Merged Queries", "FirstLevel", {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER tons", "UM"}, {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER tons", "UM.1"}),
#"Inserted Multiplication" = Table.AddColumn(#"Expanded FirstLevel", "Multiplication", each [QTY PER tons] * [PRODUCTION PLAN], Int64.Type),
Level1 = Table.RenameColumns(#"Inserted Multiplication",{{"Multiplication", "Amount"}}),
#"Merged Queries1" = Table.NestedJoin(Level1, {"COMPONENTS"}, SecondLevel, {"SKU"}, "SecondLevel", JoinKind.Inner),
#"Removed Other Columns" = Table.SelectColumns(#"Merged Queries1",{"PRODUCTION PLAN", "MONTH", "SecondLevel"}),
#"Expanded SecondLevel" = Table.ExpandTableColumn(#"Removed Other Columns", "SecondLevel", {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER BOM", "UM"}, {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER BOM", "UM.1"}),
Level2 = Table.AddColumn(#"Expanded SecondLevel", "Amount", each [PRODUCTION PLAN] * [QTY PER BOM], Int64.Type),
Custom1 = Level1 & Level2,
#"Removed Other Columns1" = Table.SelectColumns(Custom1,{"COMPONENTS", "COMPONENTS DESCRIPTION", "UM.1", "Amount", "MONTH"})
in
#"Removed Other Columns1"
Please check enclosed file.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |