In a previous post, I already blogged about case pitfalls in Power Query/M language. By default, comparisons are case-sensitive and that could be an issue when sorting list or tables but also when using the
To explain the issue, I had the following table:
Note that at the top of the table, I’ve twice the value ADO.NET, once with uppercase and once lowercase for the “Net” part. If I try to apply a
Table.Distinct, the two values will be considered as distinct and one of them won’t be removed.
Will it be an issue? If this your key and it’s part of a model where this key is part of one side of a one-to-many then it will be an issue.
The question on the table is how to manage this case? The MSDN documentation informs us that a second parameter can be defined for
Removes duplicate rows from a table, ensuring that all remaining rows are distinct.Table.Distinct( table as table, optional equationCriteria as any ) as table
But no additional info about what’s the
equationCriteria is available on this page. You’ll have more luck if you try to read the documentation for the function
List.Distinct. There, you’ve a few samples and you can understand that you’ll be able to use any function but also the
Comparer list of functions. The comparers are also documented in MSDN.
In that case, the most obvious choice is to the comparer named
Comparer.OrdinalIgnoreCase to be sure that the comparison of the different items won’t be impacted by the difference of cases.