Forum Discussion
Merging two tables in one visualisation
Hello,
Sorry this has probably been asked but I'm not sure what keywords to search for. I have one table with specific shifts worked.
| Owning Unit | Employee | Duty Date | Registered? | Shift Type | Lots of other cols not relevent to this question... |
| A&E | joe bloggs | 25/01/2021 | Registered | Night | |
| A&E | Jane Brown | 26/01/2021 | Unregistered | Night | |
| AMU | George Green | 25/01/2021 | Registered | Day |
and another table used as a lookup which tells me how many people should be working on a day, in a given unit, for a given Registration and shift type. Every day is the same and does not change.
| Owning Unit | Registered? | Shift | Value |
| A&E | Registered | Night | 2 |
| A&E | Registered | Day | 3 |
| A&E | Unregistered | Night | 1 |
| AMU | Registered | Night | 2 |
etc...
I want to merge the two tables to get the following matrix. Note Trained and Registered are the same thing.
However I'm not sure what the best way to go about this would be. I have produced a crossjoined version of the LU with dates and I tried merging fields to make a key but couldn't get much further.
Thanks
2 Replies
It seems the column Owning Unit is the column in common between both tables.
Has one of those two tables have unique values in the Owning Unit column? Could this column be used as a Primary Key for a one-to-many relationship in the data model of Power BI?
Otherwise, you could use the merge feature in Power Query to combine both tables into one based on the Owning Unit column.
Not knowing your data, not sure which solution would be best.
Maybe give us a bit more information as well as if you familiar with establishing relationships in the data model.
David
Hi scott3387 ,
Sorry for that I'm still a little confused about what would you like to do. I did a test. Is the following result what you want?
Best Regards,
Yuna
