"Remove duplicates" in M not working?

[DmitryKo]

A simple table with one column set to use as a dictionary.
Table's last transformation step is "remove duplicates".

Yet when trying to set the only column in the table - which should contain only unique values now - as a key column, the following error occurs:

How come it "doesn't have unique values" when last transformation step is exactly "remove duplicates"? This also makes it impossible to set up relationships properly, as engine thinks table having duplicates

Specific M code is autogenerated as = Table.Distinct(#"Removed Errors")


Edit: just got a repro on this manually entered table consisting of two rows:

It seems that the thing just isn't case-sensitive. Why?

[watkinnc]

Yeah, for the purpose of relationships, those are the same value. Happens to me as well with keys that end in "A" vs "a". You could Text.Uppercase the column before removing duplicates, but if you need both the uppercase and lowercase in your key, you are out of luck--you'll have to change it to something like "uat1".

--Nate

[DmitryKo]

This is definitely a bug in the product. Or a really poor design, which is essentially the same bug.

If you Distinct() it, it should be distinct - for all portions of the product, including relationships.

[DmitryKo]

Any ETA for this bug to be fixed?

[slorin]

Hi

= Table.Distinct(#"Removed Errors", Comparer.OrdinalIgnoreCase)

Stéphane 

[DmitryKo]

Not a solution. Data is case-sensitive; can't just ignore it.

What is even worse here, is tool's inconsistent behavior.

"Ignoring Duplicates" through the UI does not provide you any options such as wherever to ignore or not - it just generates the code for Table.Distinct that IS case-sensitive (which is correct). But then using the same autogenerated code should not cause issues when trying to define a table key and/or set up relationships.

Whichever (case-sensitivity) logic is used by the tool when defining dinstinctivity should be re-used by the same tool when defining ability or inability to use the same column as a key or in relationships.