cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
scott3387
Frequent Visitor

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 UnitEmployeeDuty DateRegistered?Shift TypeLots of other cols not relevent to this question...
A&Ejoe bloggs25/01/2021RegisteredNight 
A&EJane Brown26/01/2021UnregisteredNight 
AMUGeorge Green25/01/2021RegisteredDay 

 

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 UnitRegistered?ShiftValue
A&ERegisteredNight2
A&ERegisteredDay

3

A&EUnregisteredNight

1

AMURegisteredNight

2

etc...

 

I want to merge the two tables to get the following matrix. Note Trained and Registered are the same thing.

 

Capture.PNG

 

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 2
v-yuaj-msft
Community Support
Community Support

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?

v-yuaj-msft_0-1611715424411.png

 

 

Best Regards,

Yuna

 

Geradav
Responsive Resident
Responsive Resident

@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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.