Forum Discussion
Create a duplicate flag in M query
Hi Katie,
In Power Query, select your [Concatenate] column then go to the Home tab > Group By.
In the Group By dialog, create one aggregated column that uses the Count Rows operator, and another that uses the All Rows operator.
Once you apply this, you'll see that your All Rows column is a nested table. Expand this column back out to restore all your original columns.
Now, any row that has a Count Rows column value greater than 1 is a duplicate.
Pete
Hi Pete,
Thank you for this. I have followed your instructions but I have not gotten the correct outcome. What I wanted is an indicator of 0 if it is the first unique number, and 1 if it is a dupliate. Then I can filter out anything with a 1 and be left with a unique set of data points to work with in my report. Anyway, I will just use the 'remove duplicates' function to achieve my purpose. Thanks for your response though; I learned something new!
Ah, I see, sorry. I misunderstood your use-case.
Yes, remove duplicates will work just fine if all you want to do is create a unique list. Just keep an eye on which duplicated value Power Query decides to remove in case you have other columns that aren't duplicated in each row. I think it selects all duplicates after the first occurrence in the scan to delete which sounds logical, but depends on how your table is sorted.
Pete
