Forum Discussion
"Remove Duplicate" doesn't remove all duplicate
Dear all, I have a table with just one column, I tried to remove the duplicate in the column via power query. However, once I loaded to the dashboard I use count and count(distinct) both give me ...
ImkeF
Community Champion
Community ChampionSo if you want a distinct of all columns in the table, it's pretty easy:
Table.Distinct(Table, Comparer.OrdinalIgnoreCase)
Still need to figure out how to handle column-selection in it.
- ImkeF
Anonymous
Totally awe!! Thank you so much!
& so it looks with multiple columns:
= Table.Distinct(Table.FromRecords({[A="one", B=1, C=2], [A="ONe", B=1, C=3]}), {{"A", Comparer.OrdinalIgnoreCase}, {"B", Comparer.OrdinalIgnoreCase}} )ImkeF that's cool, so you could potentially mix-and-match case sensitivity? Like Column1 ignores case, Column2 doesn't? I didn't test far enough to try anything like that. I just noticed that the second argument in Table.Distinct is a list by default if you let the query editor generate the code for you, so I tried adding Comparer.OrdinalIgnoreCase to the list.
- ImkeF
Anonymous Haven't even thought of that, but computer says "yes" :-)
Table.Distinct(Table.FromRecords({[A="one", B=1, C=2], [A="ONe", B=1, C=3]}), {{"A", Comparer.Ordinal}, {"B", Comparer.OrdinalIgnoreCase}} )