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.
Hello!
I'm looking for a bit of help here. I'm newer to data merging and I'm running into a particular problem! I'm importing the data from Excel (2 tables) and there was no unique identifier with no duplicates between them, so I created a join column so that they could be merged through a left outer. It worked! BUT- in each table there is a column for 'score'. Table 2 has multiple entries for errors (line items) related to a single data point in Table 1. So, the issue is post-merge is that the score is pulled in and counted for each error or line item where I only need 1 instance of the score so that I can average the values correctly.
I have an example here.
The left visual shows an un-merged dataset and therefore what the actual average of score should be. The one on the right shows what I am dealing with post-merge. I've included COUNT of the score to show the issue. The data has to be sorted by Language,
Any help is greatly appreciated!!
@aleuer,
What is the join column? Could you please share the Excel file so that we can test? You can share Excel file via online service such as OneDrive.
Regards,
Lydia
Hi Lydia,
I've copied the truncated data here : https://sajan.box.com/shared/static/2rfhrkpm8ezmcsg4hfijadyky9yh1peg.xlsx
The troublesome columns are:
Table 1:
Join = BA
Score = C
Table 2:
Join 2= L
Score = D
i've tried simply deleting the score column from table 2, but when the merge is complete it still duplicates for each line item listed from the JOIN.
Thanks so much!
Amanda
@aleuer,
I am unable to download your file as it is removed. Could you please re-upload it?
Regards,
Lydia
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |