Forum Discussion
"Remove Duplicate" doesn't remove all duplicate
Community ChampionThis will happen when the terms have different case profiles. Pls check out if this article helps: http://www.thebiccountant.com/2015/08/17/create-a-dimension-table-with-power-query-avoid-the-bug/
Community ChampionWell, if your table just consists of one column, you can actually use this formula:
Table.ExpandListColumn(#table({"ColumnName"}, {{List.Distinct(Source[ColumnName], Comparer.OrdinalIgnoreCase)}}), "ColumnName")
It's a bit of a bugger, because the only way I found to use Comparer.OrdinalIgnoreCase (which will ignore case sensitivity) was to use it in list. So if anyone has an idea how to make this a bit smarter, you're more than welcome :-)
http://www.thebiccountant.com/2016/10/27/tame-case-sensitivity-power-query-powerbi/
- ImkeF
So 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}} )