How can I categorize within a table or Pivot Table?

[JorgeMartinez92]

Hi all, 

I am bulding the template for a food even budget. I have one table with the list of ingredients and 4 more tables with the names of the dishes, including which and how many ingredients each one requires:

I have created 1 to 1 relationships between the ingredients table and each dishes table. 

1 to 1 relationships example

I would like to create a table or a Pivot Table which has all the dishes and each ingredient necessary for their preparation. The best approach I can come up with (which I have not tried) is to merge all the dishes tables and from there create the calculated columns / measures I need. But I would like to know if there is another way of doing this. 

I do not know how this arrange would be called, so I have not had much luck researching on the internet. Can somebody please shed light upon this?

I am looking for something like this, where I could filter and see how many dishes share the same ingredient and the total cost, for example:

Result

Base table with the ingredients:

Ingrediente	Precio	Cantidad Comprada	Unidad de medida
Ajo	1,899.00	1.00	Kg
Cebolla	380.00	6.00	Kg
Pimenton	529.00	2.00	Kg
Tomate	599.00	1.00	Kg
Cebollin	200.00	1.00	Kg
Aguacate	1,999.00	12.00	Kg
Caraotas	700.00	4.00	Kg
Harina Pan	1,150.00	12.00	Kg
Platanos	750.00	7.00	Kg
Carne	3,800.00	2.00	Kg
Pollo	1,549.00	7.00	Kg
Aceite	879.00	1.00	L
Queso Amarillo	3,553.00	0.50	Kg
Queso Salado	3,600.00	0.50	Kg
Mantequilla	4,000.00	2.00	Kg
Comino	7,950.00	0.10	Kg
Mayonesa	2,330.95	0.50	Kg
Agua	200.00	8.00	L
Sal	400.00	1.00	Kg

Pepiada dish:

Ingrediente	Usado	Arepa
Ajo	0.03	Pepiada
Cebolla	0.12	Pepiada
Aguacate	10.00	Pepiada
Pollo	5.80	Pepiada
Sal	0.02	Pepiada
Mantequilla	0.10	Pepiada
Mayonesa	0.50	Pepiada

Tepuy dish:

Ingrediente	Usado	Arepa
Queso Salado	0.37	Tepuy
Platanos	7.00	Tepuy
Ajo	0.41	Tepuy
Cebolla	0.39	Tepuy
Pimenton	0.14	Tepuy
Cebollin	0.45	Tepuy
Caraotas	3.20	Tepuy
Aceite	0.20	Tepuy
Comino	0.08	Tepuy
Sal	0.02	Tepuy
Agua	1.00	Tepuy
Mantequilla	0.10	Tepuy

[JorgeMartinez92]

Thank you, it worked!

[v-xinruzhu-msft · Accepted solution]

Hi @JorgeMartinez92 

You can create three blank queries in power query, then put the following codes to Advanced Editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZHNbsMgEIRfJeKMLGxMDEfLl0pNJFftzcphm6KIygY1xoe+fVlI/JPmYGTt7Dc7sF1H6m9HKMmpVCpjDH/D93ohJ9qRRn+6vodQ4BKl/SK1ZtDWOxsqolDhLBbtww3gNSpKPTM0SBWPw+rLBOfE5VTNaVa+DVzBeRhDpYpiuWgvcDUWdi3YyOeC/efbHjxYF3mBYrXxtjiaU8lu6JoMqdMzifIWbMXWZ21iblnd73uIwtukR7erh5At8ZwKwRPPMrE4pMZ36OErte3vITZtR7Be/0wm7aSk7EnUxg3GoklFlZhN8rXJr7N6RIeCcs4yJR7n4CrmFcn5OiEfjt0s7vQH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ingrediente = _t, Precio = _t, #"Cantidad Comprada" = _t, #"Unidad de medida" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ingrediente", type text}, {"Precio", type number}, {"Cantidad Comprada", type number}, {"Unidad de medida", type text}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszKV9JRMtAzMAZSAakFmYkpiUqxOtFKzqlJ+Tk5iWBJQyM0Scf00sTkxJJUoLChAZpcAFAbyExTPQs0meDEHIhd6Mb5JuaVpBaWZsLtw5CvzM9LLYZImiJLxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ingrediente = _t, Usado = _t, Arepa = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ingrediente", type text}, {"Usado", type number}, {"Arepa", type text}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/BCsJADER/RXIupbUr6lF6FhS9LT1EDbKy3ajdPfj3bhsEI54C8zLDjLWwTzTw7IAeLwwFVGWzzOdI9/SCrrCw8xgx8JBFDTY3+Te1kls6sfcoUWsd5XoKkcPEavPH5oSZhWb4RI44VmjKuS5xJhdpMmnQcu+CFKxWiuSpIv8kXdNYWo/ZYoj0SO4z6It2bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ingrediente = _t, Usado = _t, Arepa = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ingrediente", type text}, {"Usado", type number}, {"Arepa", type text}}),
    #"Appended Query" = Table.Combine({#"Changed Type", Query2}),
    #"Merged Queries" = Table.NestedJoin(#"Appended Query", {"Ingrediente"}, Query1, {"Ingrediente"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Precio"}, {"Precio"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Query1", "Custom", each [Usado]*[Precio]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Costo"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Precio"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Arepa", "Ingrediente", "Usado", "Costo"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Arepa", Order.Ascending}, {"Ingrediente", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.