Table Merge

[MacyMolly]

Hi All,

I'm trying to merge two tables. Basically, I just want to fetch the Hours from Table B matching the Login ID and Date. I tried merging Table A and Table B, it creates duplcates. Any help is greately appreciated.

[v-rzhou-msft · Accepted solution]

Hi @MacyMolly 

Three columns Date/ Name and ID are all the key in Merge. If you only use one of them as the key in Merge, Power BI will show you all possible results when you expand the column from "Table B". So you will get duplicate results.

Use Ctrl + left click to choose multiple columns one by one as below.

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[johncolley]

Hi @MacyMolly I think the issue is the many-to-many relationship on the join. Are you able to create a distinct user table for the join - or use a bridging table as explained in this example ?

[MacyMolly]

Hi @johncolley , No Haven't tried doing it and i'm not source how to apply it with my datasets.

Here's a sample of my datasource.

datasource 

[johncolley]

Hi @MacyMolly I've created a date bridge table in this pbix. From it you can use your agent, hours and data tables to build visuals. Hope this addresses what you need?

https://www.dropbox.com/s/9g1kpsz0eo0budw/Sample%20datasource%20with%20date%20bridge.pbix?dl=0 

[johncolley]

If you just click the table option it wont duplicate rows like it does when you expand the table. 

Then you get desired result.

[MacyMolly]

Hi @johncolley ,

I have hundreds of agents so i can't just click the "Table" option.
This is what will happen when I expand the table. It creates duplicate rows.