Merging two tables in one visualisation

[scott3387]

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

[v-yuaj-msft]

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

[Geradav]

@scott3387 

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