cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dw700d
Helper V
Helper V

Merged queries resulting in duplicates

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
MonthRetailorSiteAgent ID #CreditsCredit Amount
JulyCambellsClara120$329.00
AugustCambellsClara118$245.06
JuneCambellsClara116$260.96
May

Cambells

Clara4831.51
JulyCambellsClara417107.45
JuneCambellsClara422164.01
SeptemberCambellsClara419198.34
NovemberCambellsClara4954.5
DecemberCambellsClara4785
AugustCambellsClara417128.3
OctoberCambellsClara421190.48

 

Agent Customer handled
Agent ID#RetailorSiteMonthCustomer Count
1CambellsClaraJune518
1CambellsClaraJuly594
1CambellsClaraAugust468
4CambellsClaraMay543
4CambellsClaraJune957
4CambellsClaraJuly993
4CambellsClaraAugust838
4CambellsClaraSeptember656
4CambellsClaraOctober914
4CambellsClaraNovember623
4CambellsClaraDecember385
merged table
MonthRetailerSiteAgent ID#CreditsCredit AmountID # Call HandledVend Call HandledSite Call HandledMonth Call Handled Call Count Call Handled
JulyCambellsClara120$329.001CambellsClaraJune518
AugustCambellsClara118$245.061CambellsClaraAugust468
JuneCambellsClara116$260.961CambellsClaraJuly594
JulyCambellsClara120$329.001CambellsClaraAugust468
AugustCambellsClara118$245.061CambellsClaraJune518
JuneCambellsClara116$260.961CambellsClaraAugust468
JulyCambellsClara120$329.001CambellsClaraJune518
AugustCambellsClara118$245.061CambellsClaraJuly594
JuneCambellsClara116$260.961CambellsClaraJuly594
MayConvergysClara4831.514CambellsClaraMay543
JulyConvergysClara417107.454CambellsClaraMay543
JuneConvergysClara422164.014CambellsClaraJune957
SeptemberConvergysClara419198.344CambellsClaraJuly993
NovemberConvergysClara4954.54CambellsClaraSeptember656
DecemberConvergysClara47854CambellsClaraSeptember656
AugustConvergysClara417128.34CambellsClaraOctober914
OctoberConvergysClara421190.484CambellsClaraMay543
NovemberConvergysClara4954.54CambellsClaraMay543
DecemberConvergysClara47854CambellsClaraAugust838
AugustConvergysClara417128.34CambellsClaraSeptember656
OctoberConvergysClara421190.484CambellsClaraOctober914
1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@dw700d

 

Did you select both ID and Month columns when doing the merge?

 

merge.png


Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@dw700d

 

Did you select both ID and Month columns when doing the merge?

 

merge.png


Regards
Zubair

Please try my custom visuals

View solution in original post

that did the trick thank you

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors