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.
Well, first of all, thank you for taking time to help me.
I'm sorry, but I can't really understand your answer.
1) One-to-one relation means, there is at most one entity in the tables (defined by a id/join key). It doesn't have to be exactly one. Consider my example:
Table1 und Table2 do not have exactly same join keys. We have values in Table1, which we do not have in Table2 and vice versa. The relationship is still 1:1 because we don't have more than one same value in those tables.
Then cosndier Table3, it has duplicate join key. That's why it's one-to-many, not one-to-one.
So the only "cleanse" activity I have to do is to dump duplicates, which I did in my data.
2) What you describe in your answer - to guaranteed have a value in both tables - is called "referential integrity". This is definitely not required for one-to-one-relationship.
3) Could you tell me where in my data are null values? I'm pretty sure I have none.
Good morning,
it seems that you are more expiriented than others. I just tried to explain some problems that can occur with 1:1 relationsships.
1.) and 2.) thats true, and if this is your desired result everything is fine.
3.) the main mistakes made for a 1:1 is that the undestanding that empy values "" and Null values are treated same.
4.) did you cleanse duplicates and then combined 2 Columns? Maybe thats the reason you have duplicates in your Key column in HREF
But I can't use the Query Editor cause I don't have the source files. So I have only Dax Methods to check your data