cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
misskayanne Frequent Visitor
Frequent Visitor

Problem creating unique values to create a relationship

 

Hi

 

I'm trying to join two tables together through a relationship but am having trouble. 

 

My data is of a Building (type text) , which has Floors and Rooms which can both be a mix of text and number names (type any).  

 

room mapping table 1.JPG

 

 

In order to create a relationship based on the Room column, I attempted to create a unqiue values table by referencing the Room_Mapping table, removing all by columns apart from Room, and removing duplicates. 

 

room mapping table 2.JPG

 

However, when I try to join the two tables together based on the Room column, I keep getting the following message. 

 

create relationship problm.JPG

 

I have a feeling that this error is coming because the data type is a mix of text and numbers that it is getting confused because when I go to look at the data in Data view, I can see that the data for Phase and Room fields have been converted to Text type.  

 

room mapping text type.JPG

 

Please can any one help?  I have attached a link to the workbook and data in the link below.

Room_Mapping Data and PBI Workbook

 

Many thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Problem creating unique values to create a relationship

You have case sensitive in Room on Room_UniqueValues. and have null as well. 

Action to be take

Change into Lower or Upper case. and remove null. 

 

Duplicate.PNG

5 REPLIES 5
Super User
Super User

Re: Problem creating unique values to create a relationship

Hope youn have blank in Room_UniqueVales table. Try to remove blank and try the relationship.

Highlighted
misskayanne Frequent Visitor
Frequent Visitor

Re: Problem creating unique values to create a relationship

Hi, thanks for quick reply.  I had a go at removing blanks and joining the tables again, but I get the same error message. 

Super User
Super User

Re: Problem creating unique values to create a relationship

You have case sensitive in Room on Room_UniqueValues. and have null as well. 

Action to be take

Change into Lower or Upper case. and remove null. 

 

Duplicate.PNG

Super User
Super User

Re: Problem creating unique values to create a relationship

Duplicate.PNGDuplicate1.PNG

misskayanne Frequent Visitor
Frequent Visitor

Re: Problem creating unique values to create a relationship

Thank you so much. That's solved it!