Forum Discussion
How to identify duplicate column records in Power Query
Hello,
Is there a straightforward way to identify/flag duplicate column records in Power Query?
When I say straightforward, I mean something that doesn't involve aggregations like this method for example, because I don't want to aggregate my table or add too many steps.
I'm looking for the Power Query equivalent of this DAX code.
Hi Quiny_Harl, don't be affraid of using Group By - it is one of my favourite functions.
You can achieve what you need in single step.
This code will preserve all your exicting columns and add new [Duplicate Flag] at the end.
Add this code as new step, but replace Source with your previous step reference and "PK" with your column name where you want to check for duplicates
Table.Combine(Table.Group(Source, {"PK"}, {{"All", each Table.AddColumn(_, "Duplicate Flag", (x)=> Table.RowCount(_), Int64.Type), type table}})[All])
12 Replies
Hi Quiny_Harl, don't be affraid of using Group By - it is one of my favourite functions.
You can achieve what you need in single step.
This code will preserve all your exicting columns and add new [Duplicate Flag] at the end.
Add this code as new step, but replace Source with your previous step reference and "PK" with your column name where you want to check for duplicates
Table.Combine(Table.Group(Source, {"PK"}, {{"All", each Table.AddColumn(_, "Duplicate Flag", (x)=> Table.RowCount(_), Int64.Type), type table}})[All])Anonymous, what I've envisioned is something similar to the DAX code in the link I provided. I would like to create a custom column that is going to flag each records of a Column1 that apears more than once. Then, I'm going to filter out the duplicates.
You can use implicit measures for that (Count and Count(Distinct)). Please define what you mean by "filter out the duplicates".
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
please define "duplicate" and "identify". Does that include the first occurrence? If yes then you can do a table self join.
