Forum Discussion
Remove Duplicates & Relationsship bug?
Hi guys,
I have two tables t1 and t2. They are related through column r. In t2 the values in column r are distinct, in t1 they are not. I applied the function "Remove Duplicates" (aka Table.Distinct()) on t1.r (not on the whole table), but I still can't establish a 1:1 relationship between these tables: "The cardinality you selected isn't valid for this relationship". I counted the rows and the data shrinked. I also tried to group the table by r and count the group rows - there is no group with more than 1 row...Finally I tried to recreate this scenario with dummy data and it works like it should.
Is it possible that Power BI doesn't update the meta data and thus thinks the values in t1 are still not distinct?
My data is coming from a SQL Server (import mode).
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.
14 Replies
Do you have NULL and Empty Values in this column?
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?
