Thank you for your patience WolfBiber,
With help of your example I finally found the problem...
- Power Query --> case sensitive
- Power Pivot / Power View --> case insensitive
That's why I can't find "duplicates" in Power Query, but on the canvas you see duplicate values... (it even transformed small x to capital letter). just WOW Microsoft 😞
For our example:
- HArtNr "03005.00101" is assigned to ArtDLNr "17 X 1075_64"
- HArtNr "03005.00203" is assigned to ArtDLNr "17 x 1075_64"
Possible solutions/work arounds and some stuff to read:
http://www.thebiccountant.com/2015/08/17/create-a-dimension-table-with-power-query-avoid-the-bug/
http://www.thebiccountant.com/2016/10/27/tame-case-sensitivity-power-query-powerbi/
https://community.powerbi.com/t5/Desktop/Identifying-Duplicates-by-not-taking-Case-Sensitivity-into/...
https://community.powerbi.com/t5/Desktop/quot-Remove-Duplicate-quot-doesn-t-remove-all-duplicate/td-...
Still didn't figure out how I can fix my problem. I could remove duplicates in case-insensitive manner with
= Table.Distinct(aTable, { "aColumn", Comparer.OrdinalIgnoreCase } )but I don't want to lose this distinction. ATM I'm wondering how I can get around this. But if necessary, I will open another thread for this.
