Forum Discussion
How can I categorize within a table or Pivot Table?
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 |
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.
2 Replies
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.
Thank you, it worked!
