Problem creating unique values to create a relationship

[misskayanne]

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).

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.

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

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.

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.

 

[VBA Geek]

What if you try to set the data types to Text by using Power Query before loading the data into the model?

 

[misskayanne]

Solved:

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

Action taken

Change into Lower or Upper case. and remove null in Edit Query mode.