Forum Discussion
Case Sensitivity On a One to Many Table Relationship
I know this question has been asked before. I see lots of posts about it, but I still can't figure it out. I have two tables coming from different data sources. I have created a relationship between the two tables using a column named Project. The column Project data type is text containing the names of projects.
Table 1 contains one entry of each of the following, Proc Prod and Proc PROD.
Table 2 contains many enteries of the values Proc Prod and Proc PROD.
When I create a 1 ot many relationship bewteen Table 1 and Table 2 (where Table 1 is the 1 and Table 2 is the many) it fails because Power BI doen't see Proc Prod and Proc PROD as two different values. This relationship requires that the column project in Table 1 contain unique values.
How do get Power BI to treat Proc PROD and Proc Prod as two different values so that I can create a relationship between these two tables?
Please keep in mind, these two tables are generated from two different data sources.
I think I figured this out. Or at leaset I found a workaround.
M Query it case sensitive. DAX is not. However, you can trick DAX into being case sensitive by adding a zero space character after each lower case letter. You can find the details HERE.
4 Replies
- amitchandak
Super User
Anonymous , I am hoping you are talking about column values?
You can create a new column in power query and dax
like
New column = Upper([Column])
New Column = Switch(Upper([Column]),
"PROC" , "PROD"
,"PROD" , "PROD",
"PROD"
)
I think I figured this out. Or at leaset I found a workaround.
M Query it case sensitive. DAX is not. However, you can trick DAX into being case sensitive by adding a zero space character after each lower case letter. You can find the details HERE.
JustSayJoeAdvocate IV
Not sure if this was your same scenario, but here is what I did to get my One-to-Many relationship to work.
- Edit Query on the table with the "One" of the one-to-many relationship goal.
- Click on the column name you want to join by, and click the "Transform" option, then choose the "lowercase" option.
- Click on the same column name again, and click on "Remove Duplicates"
For me, this fixed my issue and allowed me to use this table on the One-to-Many join to my table with a bunch of orders in it.
