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 different number as the same number is expected.
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 🙂
@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.
Did I answer your question? Please mark it as a solution.