Issue with text column relationship in Power BI

[Sunkari]

Are you seeing any issue while establishing a relationship based on text columns? if the text contains space, special characters or with the case sensitivity

 Please add your experience here.

[blm001]

I had the same issue.

I have a column in two different tables with text.  These "text" columns should have identical values. Therefore I should be able to make a "One to Many" releationship between the tables.  

After Upercasing all text and removing empties, I still could not get a visiual to show me data from one table based on the text column of the first table.  When I make the relationship, there is no error. It supposedly works as a "One to Many".  Nonetheless, visiuals do not see any relationship. It is as if, the data was completly unique in the related columns of the two tables.

So I copied the columns to a spreadsheet and compared (vlookup).  That work as expected. I have matches. I do not know why Powerbi doesn't see ANY of the related values.

So I made a work around.

In Power Query, on both tables I created a new column based on the columns I want to relate.  On those new columns I replaced, Quotes, Question marks and spaces with nothing (left the field empty).

I updated the relationship to use the new columns, which now look like VERYLONGGERMAN words (no spaces). 😉

Now my visuals work.

[AHPQ]

I am encountering the same issues, are there any updates for this one?

[Jayadev]

Powerquery while extracting data showing the values as in the database, while loading to the model, it converts to upper case.

And relating table does not happen !!!

[paffa88]

This is really a weird and messy issue.

Honestly, i could not understand how is it possible that MS didnt fix it yet,

We built a complex architecture and BI solution and now during the final testing we discovered that basically do not work due to many keys recognized as the same one.

Why, if the DB (SQL Server) and Power Query are case sensitive, is there in the middle a piece that is case insensitive? i could not really understand.

Did you find any solution meanwhile? At the moment we are adding space at the end for the duplicate key but this is just a workaround.

[Anonymous]

Unfortunately, this did not surprise me when I encountered it.  MSFT's emphasis seems to be on pretty reporting, rather than focusing on the data engine.  There are a great many requests still outstanding, which ask them to fixi the data engine, and make it easier to manipulate the data.

[Js29]

I have the same issue, salesforce uses case sensitive text IDs and power bi does not honor those in relationships

[Anonymous]

When you bring your IDs into Power BI, simply change them into a single case, such as upper case.

[Js29]

Not an option, the ids are real valid case sensitive ids.
I have sales force tables with more than 15M records...
We have more than 2k licenses, i will open a support big

[cemaltevrizci]

I trimmed, cleaned and made everything uppercase but power bi can't find the matching items.

[v-jiascu-msft]

@Sunkari

Hi,

Yeah, when creating relationships, Power BI ignore the space and the case sensitivity while Remove Duplicates doesn't. So we can't create relationships due to no column has unique values even we have removed the duplicates. Maybe a bug. We are working on it. What happened to special characters? (which ones)

Best Regards!

Dale 

[Sunkari]

@v-jiascu-msft: Thanks for the response. There is a bug with a text column relationship, I just wanted to bring your notice.

[vanessafvg]

power query is case sensitive and dax isn't so if you clean your data in power query sometimes it looks unique but when you try to link it the following creates issues

1 any linking text columns that have different cases would look the same with relationships in dax, but power query would have seen it as unique, best thing to do is to change all the text to upper or lower case in power query

2 any blank records also create issues in dax, pad empty / null values with a 'unknown' or something to resolve that issue

[Anonymous]

What do you mean? When you link 2 tables via columns?  If the column values don't match exactly you will have difficulties.