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

     

     

Quiny_Harl's avatar
Quiny_Harl
Advocate III
2 years ago

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.

lbendlin's avatar
lbendlin
Super User
2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    If you are going to filter out the duplicates anyway, why not just click "Remove Duplicates"?

     

    --Nate

    • Quiny_Harl's avatar
      Quiny_Harl
      Advocate III
      2 years ago

      Anonymous, because this works only when the whole table row, including all columns, is a duplicate.

  • Quiny_Harl's avatar
    Quiny_Harl
    Advocate III
    2 years ago

    I cannot use Count and Count(Distinct) in Power Query.
    I'm not sure how to further clarify what I need as I think it is pretty obvious.

    PKDuplicate Flag
    11
    21
    32
    32
    41

     I need to add the Duplicate Flag column in Power Query. Each record that has a count more than once is a duplicate. I'm going to apply a filter on the  Duplicate Flag to remove these records.

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkikiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PK = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Duplicate Flag", (k)=> List.Count(List.Select(Source[PK], each _ = k[PK] )))
in
    #"Added Custom"

       

      You can throw a List.Buffer into the mix if you need better performance.

       

      • Quiny_Harl's avatar
        Quiny_Harl
        Advocate III
        2 years ago

        Thank you for trying to help me but I don't understand how to use this code.