Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to merge two queries. The first query has Agents Credits given data. This query Identifies how many credits an agent gave each month they were employed. This query includes 4000 different agents and most of these agents have multiple entries because they have worked for multiple months. The second query Identifies how many customers each agent saw. This query includes 4900 different agents and most of these agents have multiple entries because the agent has worked multiple months. My objective is to have one complete data set that includes credits given for all agents and customers seen for all agents. I choose the left outer join which matched 20,792 out of the first 27,967 rows. My results came back with an extraordinary amount of duplicates . For instance ID # 1 has only three entrees in each individual table but after both tables are merged it now has 9 entrees. ID# 4 Has 8 entries in each table but 66 entries in the merged table. What am I doing wrong? Below is an example of the two individual tables and the merged table
Agent Credits | |||||
Month | Retailor | Site | Agent ID # | Credits | Credit Amount |
July | Cambells | Clara | 1 | 20 | $329.00 |
August | Cambells | Clara | 1 | 18 | $245.06 |
June | Cambells | Clara | 1 | 16 | $260.96 |
May | Cambells | Clara | 4 | 8 | 31.51 |
July | Cambells | Clara | 4 | 17 | 107.45 |
June | Cambells | Clara | 4 | 22 | 164.01 |
September | Cambells | Clara | 4 | 19 | 198.34 |
November | Cambells | Clara | 4 | 9 | 54.5 |
December | Cambells | Clara | 4 | 7 | 85 |
August | Cambells | Clara | 4 | 17 | 128.3 |
October | Cambells | Clara | 4 | 21 | 190.48 |
Agent Customer handled | ||||
Agent ID# | Retailor | Site | Month | Customer Count |
1 | Cambells | Clara | June | 518 |
1 | Cambells | Clara | July | 594 |
1 | Cambells | Clara | August | 468 |
4 | Cambells | Clara | May | 543 |
4 | Cambells | Clara | June | 957 |
4 | Cambells | Clara | July | 993 |
4 | Cambells | Clara | August | 838 |
4 | Cambells | Clara | September | 656 |
4 | Cambells | Clara | October | 914 |
4 | Cambells | Clara | November | 623 |
4 | Cambells | Clara | December | 385 |
merged table | ||||||||||
Month | Retailer | Site | Agent ID# | Credits | Credit Amount | ID # Call Handled | Vend Call Handled | Site Call Handled | Month Call Handled | Call Count Call Handled |
July | Cambells | Clara | 1 | 20 | $329.00 | 1 | Cambells | Clara | June | 518 |
August | Cambells | Clara | 1 | 18 | $245.06 | 1 | Cambells | Clara | August | 468 |
June | Cambells | Clara | 1 | 16 | $260.96 | 1 | Cambells | Clara | July | 594 |
July | Cambells | Clara | 1 | 20 | $329.00 | 1 | Cambells | Clara | August | 468 |
August | Cambells | Clara | 1 | 18 | $245.06 | 1 | Cambells | Clara | June | 518 |
June | Cambells | Clara | 1 | 16 | $260.96 | 1 | Cambells | Clara | August | 468 |
July | Cambells | Clara | 1 | 20 | $329.00 | 1 | Cambells | Clara | June | 518 |
August | Cambells | Clara | 1 | 18 | $245.06 | 1 | Cambells | Clara | July | 594 |
June | Cambells | Clara | 1 | 16 | $260.96 | 1 | Cambells | Clara | July | 594 |
May | Convergys | Clara | 4 | 8 | 31.51 | 4 | Cambells | Clara | May | 543 |
July | Convergys | Clara | 4 | 17 | 107.45 | 4 | Cambells | Clara | May | 543 |
June | Convergys | Clara | 4 | 22 | 164.01 | 4 | Cambells | Clara | June | 957 |
September | Convergys | Clara | 4 | 19 | 198.34 | 4 | Cambells | Clara | July | 993 |
November | Convergys | Clara | 4 | 9 | 54.5 | 4 | Cambells | Clara | September | 656 |
December | Convergys | Clara | 4 | 7 | 85 | 4 | Cambells | Clara | September | 656 |
August | Convergys | Clara | 4 | 17 | 128.3 | 4 | Cambells | Clara | October | 914 |
October | Convergys | Clara | 4 | 21 | 190.48 | 4 | Cambells | Clara | May | 543 |
November | Convergys | Clara | 4 | 9 | 54.5 | 4 | Cambells | Clara | May | 543 |
December | Convergys | Clara | 4 | 7 | 85 | 4 | Cambells | Clara | August | 838 |
August | Convergys | Clara | 4 | 17 | 128.3 | 4 | Cambells | Clara | September | 656 |
October | Convergys | Clara | 4 | 21 | 190.48 | 4 | Cambells | Clara | October | 914 |
Solved! Go to Solution.
Did you select both ID and Month columns when doing the merge?
Did you select both ID and Month columns when doing the merge?
that did the trick thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |