cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eniX Member
Member

Re: Remove Duplicates & Relationsship bug?


@WolfBiber wrote:

Hey,

you have duplicates e.g.: "17 X 1075_64" in HREF_preprocessed

 

 


relation_bug_possible_duplicates.png

 

^^

 


@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

Unbenannt.png

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?

eniX Member
Member

Re: Remove Duplicates & Relationsship bug?

BTW I can create your "TT" calculated table without an error.

WolfBiber Member
Member

Re: Remove Duplicates & Relationsship bug?

In the example data you gave me you have duplicates:

Unbenannt.png

eniX Member
Member

Re: 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 Smiley Sad

 

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.