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...
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"
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)
    • diablo9083's avatar
      diablo9083
      Frequent Visitor
      1 year ago

      This works perfectly. Follow-up though. Can I not filter a custom column? I just tried to filter as the next step, and I get a "Expression.Error: A cyclic reference was encountered during evaluation."

      The step in question is just this basic filter:
      = Table.SelectRows(#"Added Custom", each ([Duplicated] = null))

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

        Hi diablo9083 ,

        You need to break the dependency chain by materializing the custom column as a new step before applying the filter.

         

        After added Custom Column (Duplicated) above and before filter the data, please add a step to materialize the data (this ensures the evaluation of the Duplicated column is complete before proceeding):

        #"Materialized Custom" = Table.Buffer(#"Added Custom")

         

        Now, you can safely filter the rows:

        #"Filtered Rows" = Table.SelectRows(#"Materialized Custom", each [Duplicated] = null)

         

        The Table.Buffer function creates a snapshot of the table at the current step, preventing Power Query from trying to dynamically reevaluate the Duplicated column while applying the filter. This avoids the cyclic reference error.