Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
boldwake
Frequent 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

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/

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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"

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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/

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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" 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.