How to create relation in different table

[ismailRajiwate]

Hello Expert.
Need your expert advice to extract the output based on three tables.
The sample("SampleA100.png") data is attached to this message.

1st Table is the List of Finished Product and it Qty produced.
2nd Table has the Recipe for each Finished Product. The ingredients list contain some semi-Finished products whose ING_ITeM code starts with "2-"
3rd Table has the Recipe for Semi-Finished Products.

By using the above information how I can get the output to show total consumption as shown in the "Required output" Table.
Kindly advise me, what are the best way and how I can achieve it.
Let me know if you need further details.

Thanking you in anticipations.
Ismail

 

[jorismoerings]

Hi,

Load your tables into power query and left join them correctly.
Your M query in advanced editor should look something like this:

let
    Source = Table.NestedJoin(Production_List, {"FINISHED_PROD_CODE"}, Finished_Product_Receipe_List, {"FINISHED_PROD_CODE"}, "Finished_Product_Receipe_List", JoinKind.LeftOuter),
    #"Expanded Finished_Product_Receipe_List" = Table.ExpandTableColumn(Source, "Finished_Product_Receipe_List", {"ING_ITEM", "QTY"}, {"ING_ITEM", "QTY.1"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Finished_Product_Receipe_List", {"ING_ITEM"}, Semi_Finished_Product_Recipe_List, {"SF_RECEIP_CODE"}, "Semi_Finished_Product_Recipe_List", JoinKind.LeftOuter),
    #"Expanded Semi_Finished_Product_Recipe_List1" = Table.ExpandTableColumn(#"Merged Queries", "Semi_Finished_Product_Recipe_List", {"SF_RECEIP_CODE", "ING_ITEM", "QTY"}, {"SF_RECEIP_CODE", "ING_ITEM.1", "QTY.2"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Semi_Finished_Product_Recipe_List1", "Total", each if [SF_RECEIP_CODE] is null 
then Value.Multiply([QTY],[QTY.1])
else Value.Multiply([QTY], Value.Multiply([QTY.1],[QTY.2]))),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"FINISHED_PROD_CODE", Order.Ascending}, {"ING_ITEM", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Total", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"QTY.1", "QTY.2"})
in
    #"Removed Columns"