Forum Discussion
How to Easily Locate Duplicates in a Row After Merging Two Datasets ?
Good afternoon,
As the title suggests, I have combined two datasets based on a shared commonality (in this case account id #).
Now that the datasets are merged, I have columns from the first dataset and columns from the second dataset.
I am wanting to see whether I have duplicates in the rows after combining the datasets.
For example,
say dataset one has the following columns - Account_Id | $amount | state | city
and dataset two has the following columns - Id | AMOUNT | state | county
then after merging the two based on Account_Id, I'm supposed to have the following table in query:
Account_Id | $amount| state | city | AMOUNT | state | county
Obviously, it's easy to see the duplicates in such a small array. But my dataset is very large.
Any suggestions?
Thank you!
5 Replies
- edhans
Community Champion
One way is this:
- Add an Index column to the first table before you merge
- Do the merge and expansion
- Group by on the Index column with two aggregations:
- A Count Rows (the default on a group)
- add an All Rows aggregation.
- Filter that 3 column table to only show where the Count > 1.
Those are your dupes. You can then either expand the "All Rows" column to see the dupe, or just click on the field and it will show the duplications in the bottom of the Power Query window.
Of course delete all of the grouping and stuff after done - this is just for analysis.
You can quickly see if there are even any dupes by using the Transform Tab, Count Rows feature before and after the merge. If the record count is the same, there are no dupes. If it is different though you will know you have dupes but no clue where, so you can then do the above steps.
You could do a Distinct on the Index after the merge, but you cannot guarantee which records will be kept/removed. It will keep one and discard the others.
Thank you for your quick reply and suggestion!
I think I understand what you suggested, but I am not sure if it allows me to achieve my desired outcome; I think I might have not explained it properly (my bad).
When I merged the two data sets, I am expected to see duplicate columns. Like in the example in my first message, I will see something like Account_Id | $amount| state | city | AMOUNT | state | county . In this case the $amonut and AMOUNT columns are the duplicates, where I am expecting to see the same values in each row.
I want to find these duplicates ^. Doing what you suggested, I got a count of '1' across the board, but I'm not sure how to intrepret it. I know that one dataset has 206 columns while the other 198. I'm trying to find the distinct ones and I expected to see 198 duplicates and 8 distinct.
Hopefully I'm explaning this clearly (?)
Thanks again
- edhans
I'm not sure how you could easily program that kind of duplicate detection. If you had Amounts and Amounts.1, which is what happens when you merge two tables with the same column names then it wouldn't be too hard. You could use the logic above once you convert the column names to a field. Table.ColumnNames() can do this.
For example, I expanded Date and DateKey from another table in to my Date table and it created duplicates with the .1 after them. Using Group By with the All Rows logic, I can see I have Date and Date.1
But to do that you need a common name - I used the Text.BeforeDelimiter() on the column to get everything before the period. But you cannot do that if your columns are $amount and AMOUNT unless it is consistently like that, and you can get the text after the $ sign, and then upper case all of them. Power Query is case sensitive, so Amount, AMOUNT, and amount are 3 different values. (Those are just one in DAX).
