cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
boldwake Regular Visitor
Regular Visitor

PowerBI disagrees that my values are Unique

HI experts,

 

 

I am new to PowerBI but absolutley LOVE it.   I have been able to successfully join and create relationships across a number of my data tables.  However, I just encountered an error saying that I can't create a relationsihp between two tables bc i need to have unique values in one of the tables.  I fully understand this concept and have crated successfully linked tables before.  My issue is that I am fairly certain that PowerBI is wrong and that I have unique values. 

 

Issue:

1) imported data with that had sales and two additional columns a "Sales_Cat_ID" and a name "Sales_Cat Name".  Typically, there are 9 "Sales_Cat_ID"s and anything else should be grouped into "Sales_Cat_ID" = "867", which has the "Sales_Cat_Name" = "OTHER".  However the data I pulled in had 85 "Sales_Cat_IDs" (i.e. 9 standard categories & 1 other category"   The remaining 75 sales_cat_ids are all mistakes and should be grouped into "Other"

 

2) I used Power BI to give me a unique list of "Sales_Cat_ID"s that I opened in excel and added a name column for the 9 categories and made the descriptoin "Other" for all the other categoriess

 

3) I double checked that my values for "Sales_Cat_ID" were unique in excel and importated the new excel table into PowerBi

 

4) when I tried to join this new unique table with an exisitng one, Power BI keeps telling me that at least one column in one of the tables needs to be unique, which is confusing me bc I'm reallly certain that the table i imported has only unique values for "Sales_Cat_ID" bc (A) i pulled it from Power BI and (B) I double chedked that there were no duplicates in excel before importing the table. 

 

Any ideas as to what I am doing wrong?


Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: PowerBI disagrees that my values are Unique

This might be due to the different handling of case sensitivity in Power BI.

The query editor (M) is case sensitive while the data model (DAX) isn't:

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/

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

5 REPLIES 5
Super User
Super User

Re: PowerBI disagrees that my values are Unique

Check for blanks in the list too. Blanks can grow the same error and are not allowed. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
boldwake Regular Visitor
Regular Visitor

Re: PowerBI disagrees that my values are Unique

Thanks for the super fast response.  I think that blanks were causing it but i couldnt figure out how to resolve it so i converted the table that i made to replace the values from excel to a text file via notepad and was able to get PowerBI to recognice that this new table had unique values and carete the relationship.  But now i have a separate issue, I have two catch all buckets now "Other::" and "Blank or Null"

 

Is there a away to change Blank / Null values to Other?  the idea being i want to have one "Other" Category that act as a catch all for both those values that I identified as "other" and anything that PowerBI has listed as "Blank"

Super User
Super User

Re: PowerBI disagrees that my values are Unique

You should be joining the tables using a unique key. On the 1 side of the relationship you are not allowed duplicates, so I don't see why you would want to substitute any values. On the many side of the relationship you can have what every you like, but it should match the keys on the one side. 

 

Can you  explain your data and why it has blanks on a key column?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
boldwake Regular Visitor
Regular Visitor

Re: PowerBI disagrees that my values are Unique

Sure. So the data is really 16MM rows.  It has Salses Category ID and Sale Category Name columns that should autopopulate based off of the brand name for the sale.

 

However, sometimes the sales Sales category ID / sales category name can be manually inputed but 99.99999% of the time it is done inaccuratley and why I want it in "Other" this is what caused me to have 85 category IDs / rather than 10.  However, if the brand field is not inputed or can not be matched in our systems, then the systel will leave a "Blank" or "Null" Value in the sales cateogry Id and name fieldss.  

 

Basically I have some poeple entering what they think is right in the sales cateogry ID and name fields and others that don't want to guess and becuase the brand is "unknown" the system leaves the Category ID and name field blank.   I want to combine the manually inputed data errors and the system generated "Blank" / "Null" errors into one single category called "Other" 

Super User
Super User

Re: PowerBI disagrees that my values are Unique

This might be due to the different handling of case sensitivity in Power BI.

The query editor (M) is case sensitive while the data model (DAX) isn't:

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/

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)