Forum Discussion

diablo9083's avatar
diablo9083
Frequent Visitor
1 year ago
Solved

M - Created Column to Tag Duplicates

I'm trying to use M to create a column that will tell me when values in another column are duplicated. My end result is I want to use this column to filter out data in a subsequent step.

 

Prior to this, I tried using the Group By method to tag duplicates, but my table has 5.5 million rows, and doing the Group By and transform seemed to hang and just spin and spin and spin in the UI.


I came up with this: 

= Table.AddColumn(#"Added Index", "Duplicated", each if List.Count(List.FindText(Sales[Completed Order ID], [Completed Order ID])) > 1 then "Duplicated" else null)

I get the results I expect and duplicated values get tagged with "Duplicated" in this new column, but the custom column is underlined in red and says that 100% of values are errors.

Could it be complaining that Sales[Completed Order ID] is a column, and not a list, and FindText can't use it properly?

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi diablo9083 ,

    Please consider to use this query, if not works, please provide the sample data and the desired output. make sure that the data can be pasted in excel:

    let
    // Step 1: Reference your existing Sales table
    #"Added Index" = Sales,  // Replace this with your starting table if needed

    // Step 2: Extract the Completed Order ID column as a list
    CompletedOrderList = Table.Column(Sales, "Completed Order ID"),

    // Step 3: Add a custom column to tag duplicates
    #"Added Custom" = Table.AddColumn(#"Added Index", "Duplicated", each 
        if List.Count(List.Select(CompletedOrderList, (x) => x = [Completed Order ID])) > 1 
        then "Duplicated" 
        else null),

    // Step 4: Filter rows to remove duplicates
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Duplicated] = null)
in
    #"Filtered Rows"

9 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    Hi diablo9083 ,

    Why not just remove duplicates easily like this?

    1- Select column that contain duplicates, choose remove rows and remove duplicates as shown bellow:

     

    • diablo9083's avatar
      diablo9083
      Frequent Visitor
      1 year ago

      Hi Bibiano_Geraldo ,

      I want to remove them conditionally based on blanks found in yet another column, so I'm trying to tag them as an intermediary step.

      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Icon for Super User rankSuper User
        1 year ago

        Hi diablo9083 ,

        Understood now, but in Power Query M, you can't directly reference a column like that; you need to convert it into a list first before applying list functions like List.FindText.
        The issue you're facing arises because List.FindText expects a list of values to search within, but you're trying to use a column reference (Sales[Completed Order ID]) instead of a list.

         

        Please try the bellow code and let me know if its all OK:

        = Table.AddColumn(#"Added Index", "Duplicated", each 
    if List.Count(List.Select(Sales[Completed Order ID], (x) => x = [Completed Order ID])) > 1 
    then "Duplicated" 
    else null)