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.
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
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.
In the example data you gave me you have duplicates:
Hey,
you have duplicates e.g.: "17 X 1075_64" in HREF_preprocessed
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
WolfBiber wrote:Hey,
you have duplicates e.g.: "17 X 1075_64" in HREF_preprocessed
^^
WolfBiber wrote: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
But neither I have null values in my data, nor duplicates. That's why the topic name contains "bug?"...
4) --> I created new calculated column, concatenating two strings. I removed duplicates after the creation of the column. I also tried both to transformate my data directly in Power BI and outside of it, with Jupyter Notebooks. Same result.
Would it help you if I gave you the data as well?
BTW I can create your "TT" calculated table without an error.
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.
Absolutely incredible that a 6 year old bug with thousands of view and an incredible amount of threads created about it still has not been fixed.
