Forum Discussion
Relating two tables [Help]
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?
Just three hoppers, that doesn't change.
