Forum Discussion
Relating two tables [Help]
Hello All,
I need help trying to figure this one out:
I have two tables that have the following columns
Materials
Num Type Supplier
1. A John
2. B John
3. C John
4 A Dan
5. B Dan
6. C Dan
7. A Peter
8. B Peter
And so on, I use the same three raw materials for production, but from different suppliers with different characteristics and prices.
I also have a production table with all the information regarding operations of the machinery, which includes the following columns:
Management
ID Date Shift Material in Material in Hopper2 Mat in Hopper3 Kg Consumed H1 Kg Cons H2 Kg Cons H3
. Hopper1
1. 12/1/2023. 1 2 1 5 300 400 500
2 12/1/2023 2 3 1 4 320 280 600
3. 13/1/2023 1 1 2 3 250 300 500
Im trying to create a relation in which I can summarize consumption of each material per shift. I dont know if Im drowning in a glass of water here but im new to Power Bi and Im self-taught, so any help would be appreciated.
Let me know if more information is necessary.
Thanks in advance
6 Replies
Here is your source data in slightly more usable format:
Materials:
Num Type Supplier 1 A John 2 B John 3 C John 4 A Dan 5 B Dan 6 C Dan 7 A Peter 8 B Peter Management:
ID Date Shift Mat H1 Mat H2 Mat H3 Kg Cons H1 Kg Cons H2 Kg Cons H3 1 12/1/2023 1 2 1 5 300 400 500 2 12/1/2023 2 3 1 4 320 280 600 3 13/1/2023 1 1 2 3 250 300 500 However, the management table is not usable in that format. A better format would be the one below. That way you can link both tables via the Material Number.
ID Date Shift Hopper Material Kg 1 12/1/2023 1 1 2 300 2 12/1/2023 1 2 1 400 3 12/1/2023 1 3 5 500 4 12/1/2023 2 1 3 320 5 12/1/2023 2 2 1 280 6 12/1/2023 2 3 4 600 7 13/1/2023 1 1 1 250 8 13/1/2023 1 2 2 300 9 13/1/2023 1 3 3 500 Next step is to load this into the Power BI data model
Ideally you also have a calendar table in your data model
From there your report visuals nearly write themselves
see attached
Thanks for the response!
I assumed it was not possible...
This is a database coming straight from an automated production, so I would have to ask the manufacturer of the machinery to modify the database structure.Given the fact that its a database structured by the manufacturer, is there any chance I can rearrange the data I already have to get to the same solution?
I spoken to them, but Im not sure if they are going to make changes to their structureDo you always have three hoppers or is that number of columns variable?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY3BDcAwCAN34R0pYELVXVD2X6NA0ip9cBgZy+4k1EjQpYOhqWOwt8Uoc3AULTibL//I5P1mR2rkN+7ktTPl679HjiyMv7bqmQ8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Shift = _t, #"Material in Hopper1" = _t, #"Material in Hopper2" = _t, #"Mat in Hopper3" = _t, #"Kg Consumed H1" = _t, #"Kg Cons H2" = _t, #"Kg Cons H3" = _t]), #"Removed Other Columns1" = Table.SelectColumns(Source,{"ID", "Date", "Shift", "Material in Hopper1", "Kg Consumed H1"}), #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Hopper", each 1), #"Renamed Columns1" = Table.RenameColumns(#"Added Custom1",{{"Material in Hopper1", "Material"}, {"Kg Consumed H1", "Kg consumed"}}), #"Removed Other Columns2" = Table.SelectColumns(Source,{"ID", "Date", "Shift", "Material in Hopper2", "Kg Cons H2"}), #"Added Custom2" = Table.AddColumn(#"Removed Other Columns2", "Hopper", each 2), #"Renamed Columns2" = Table.RenameColumns(#"Added Custom2",{{"Material in Hopper2", "Material"}, {"Kg Cons H2", "Kg consumed"}}), #"Removed Other Columns3" = Table.SelectColumns(Source,{"ID", "Date", "Shift", "Mat in Hopper3", "Kg Cons H3"}), #"Added Custom3" = Table.AddColumn(#"Removed Other Columns3", "Hopper", each 3), #"Renamed Columns3" = Table.RenameColumns(#"Added Custom3",{{"Mat in Hopper3", "Material"}, {"Kg Cons H3", "Kg consumed"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1" & #"Renamed Columns2" & #"Renamed Columns3",{{"Date", type date}, {"Kg consumed", type number}, {"Hopper", type text}},"en-GB") in #"Changed Type"
