Limitation on text length when creating relationship?

[dedelman_clng]

I have a data model where the dimension table has a key value that is text (I have verified in Excel that there are no duplicates).  However, when I try to create a relationship to the fact table using that text field, I get the "one table must have unique values" error.

48 of the 218 records in my dimension table have a text key length over 100 characters.  Is there a limit as to how long a text field can be in order to be part of a relationship?

Another bit of info: when looking at the data in the PowerBI data view (not the query view), I can't make the column wider past about 60 characters.

Unfortunately this is data coming from a 3rd party provider, so replacing the text keys with index keys would likely be out of the question (as the fact table would have to be altered as well).

Any help would be appreciated.

[v-ljerr-msft]

 Hi @dedelman_clng,

---

48 of the 218 records in my dimension table have a text key length over 100 characters.  Is there a limit as to how long a text field can be in order to be part of a relationship?

---

I have verified that I can create relationships between tables that have a text key length over 200 characters on my side. In addition, I haven't been aware of any official documentations/articles which mention that there is a limit as to how long a text field can be in order to be part of a relationship. So I don't think there is.

In this scenario, could you share a sample pbix file which can reproduce the issue? So that we can help to do some investigation on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[dedelman_clng · Accepted solution]

Thanks for the reply.  Given that there is no documented limitation, I took a stab at the data model again, removing any leading special characters from the columns (all of the large text values started with a / ).  Once I removed the leading special character, the data model actually self-detected the correct relationship.

So moral of the story - don't have your relationship index fields start with a special character (or at least a /)

[cnpdx]

What is the technique to remove all non-alphanumeric characters? In my case it could be many possibilities, so I can't do a simple trim that isolates 1 or 2 characters specifically. Thanks for your help!

[dedelman_clng]

"What is the technique to remove all non-alphanumeric characters? In my case it could be many possibilities, so I can't do a simple trim that isolates 1 or 2 characters specifically. Thanks for your help!"

@cnpdx that is generally best done in PowerQuery/M.  You should probably create a new post with that specific question and the users most familiar with advanced PowerQuery should be able to help.