Duplicate values when creating one to many relationship in Power Bi

[ASteele]

I am trying to create a relationship between tables but I get a duplicate values error on Product ID. See the example below.

Product	Product ID
Product A	a1s3d000000Ipsd
Product B	a1s3d000000IPSd

How do I get around this error and still create a relationship per the values above? Is this possible?

Thanks

[ASteele · Accepted solution]

Hello ToodChitt,

Thanks for all of your responses. I was hoping there was a workaround for this. I still need to review the Chris Webb article as a possible solution. I did try and add a column with an idex, but wasn't sure how to use that to relate back to the Requirement ID. Thanks again for your assistance and I will mark 'Accept as Solution'

[ToddChitt]

Unfortunately, Power BI is CASE INSENSITIVE when it comes to the data. I don't like it, but there is not much I can do about it. I feel your pain. Your source data is CASE SENSITIVE, so "CuCR" does NOT equal "Cucr", but Power Query doesn't care. What is worse, the Power Query coding language (M) IS Case Sensitive.

Go figure. Thank Microsoft.

Check Chris Webb's article here for ideas: Chris Webb's BI Blog: Power BI And Case Sensitivity Chris Webb's BI Blog (crossjoin.co.uk)

[ASteele]

So if the only difference in a scenario where the Requirement ID ends with 'CuCR' on one row and on another row it ends with 'Cucr' this can not be used on the left side or the 'ONE' to many side of the table relationship? In Power Bi, is that correct?

[ToddChitt]

This is simply a test to verify that you do, or do not, have duplicates on any one particular field. We know you have duplicates. 

Now you need to figure out WHY. They are coming in from the source. What is that source, and what transformations are you doing on it? What can you tell us about that table?

[ASteele]

I know where the issue is in the data. Power Bi is case insensitive. It views 'CuCR' and 'Cucr' as duplicates. The data is coming from Salesforce and will always have rows where this is the case. Would have to manipulate at least one of the rows where the duplication exists? That could be half of the 133 duplicates on this file. Is this the correct solution for this issue? Find the duplicates and make unique.

[ASteele]

TodChitt,

Here are the resulsts in Power Bi Table Visual. Thanks for providing this example. I'm learning Power Bi in the process.

[ASteele]

GM ToddChitt,

I used Conditional Formatting in Excel to find the duplicates and here are the results. 266 duplicates out of 14,236 rows.

When I did the Count function, it returned 0 for Count and Count Unique (in Excel).

[ToddChitt]

A) You are using Excel COUNT functions which are NOT DAX functions. Try COUNTA in Excel.

B) You have proven or admitted that you have duplicates. You need to remove those before that column can be on the ONE side of a Many-to-One relationship. And that needs to be done at the source. 

[ToddChitt]

First verify if you DO have duplicates. Do I have duplicates in my data:

Here's a quick visual to check a very large table:

Count of Values <> Distinct Count. Herefore I have duplicates. 

How bad is your duplicate problem? Do you have 1,000,000 rows and ONE duplicate? or 1,000,000 rows with 500,000 duplicates?

I have worked with many clients over the years. I often hear, "Yes, that field should be absolutely unique in that table, NO duplicates." Then when the load fails, I spin up a simple visual like this and PROVE to them that they do, in fact, have duplicates. They are always supprised.

So, back to the question, how MANY duplicates do you have? Is it a single errant row? 

It will probably need to get fixed in the source query or source data.

[ToddChitt]

You can't. On the ONE side of a Many-to-One relationship, in order for the relationship to be made ACTIVE, you MUST have unique values in the column that participates in the relationship. If you attain that, and then on a future data refresh you introduce duplicates in that column, the load/refresh will fail.

Do this: For the table where you are supposed to have unique values, create a simple TABLE visual and add that column to the visual THREE times. Leave the first instance of it alone, on the other two, make the aggregations COUNT and COUNT DISTINCT. Check the grand totals at the bottom, if the COUNT number equals the COUNT DISCTINCT number, then you have uniqueness. If not, sort by COUNT Descending to fine the offenders.

[ASteele]

ToddChitt, thanks for prompt response.

I'm not sure how creating the additional columns will help. How can I make the values unique? I tried adding a column with an index for each row. But not sure how to relate that back to the Product ID column so that I can ultimately find the correct Product for my report.

Does that make sense?