Forum Discussion
M - Created Column to Tag Duplicates
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"
Super UserHi 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)
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
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.
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
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"