Forum Discussion

Quiny_Harl's avatar
Quiny_Harl
Advocate III
2 years ago
Solved

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...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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])

     

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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])