cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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?

Highlighted
Helper III
Helper III

Re: Remove Duplicates & Relationsship bug?

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

Highlighted
Microsoft
Microsoft

Re: Remove Duplicates & Relationsship bug?

In the example data you gave me you have duplicates:

Unbenannt.png

Highlighted
Helper III
Helper III

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 😞

 

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.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors