Relating two tables [Help]

[carlosbsantana]

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

[lbendlin]

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"

[lbendlin]

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

[carlosbsantana]

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 structure

[lbendlin]

Do you always have three hoppers or is that number of columns variable?

[carlosbsantana]

Just three hoppers, that doesn't change.

[carlosbsantana]

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.