Forum Discussion
Remove Duplicates & Relationsship bug?
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/
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.
No sir.
In the meantime I loaded the data into jupyter notebook and preprocessed it there. I loaded transformed data into Power BI and tried to establish a relationship - it's still n:1...So the problem seems not to arise from using the "Remove Duplicate" function. Still have no clue :/
Hi,
can you share some example Data as PBIX?
Sure, if you tell me how do I do this?
just upload the file to some cloud storage like onedrive and share the link
ok, as you can see you have more data rows in one table than in the other
and you have ArtDLNR in one table which isnt present in the other, and vice versa.
and you have many rows with Empty ArtDLNr (not a unique constraint),
thats not a 1:1.
You have to cleanse your data or keap it as 1:*
Ich wünsche Ihnen viel Erfolg
Viele Grüsse aus HH