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 ,

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. 

 

 

diablo9083's avatar
diablo9083
Frequent Visitor
1 year ago

I think I spoke too soon, and your formula replacement evaluated the same as my original, but it also has the same flaw. It underlines in red and while it evaluates fine, it says 100% of the results are errors. Any ideas as to why that might be? Might I need some sort of let statement where I somehow cast the column as a list? 

Sorry to be a be confusing.

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    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"
    • diablo9083's avatar
      diablo9083
      Frequent Visitor
      1 year ago

      I think this did it! Thanks for your help.