cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
purnima13 Regular Visitor
Regular Visitor

Relationship text to text with trailing spaces


We have two tables in which technically they are unique values in the database on both ends.
Please notice that one of them have a trailing space.
Power BI does not allow me to create a relationship between the two as it does a trim before.
Is there any way other than deleting a row to create a relationship?
Goal is to keep all rows and consider both rows as unique

table 1:
"AB123 " 
"AB123"

table 2
"AB123 " 
"AB123"

5 REPLIES 5
Super User
Super User

Re: Relationship text to text with trailing spaces

@purnima13 Please try creating a unique hexadecimal value in the database (which will be unique for each text with space and without space) and use this as a relationship link between tables.



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





MarkLaf Member
Member

Re: Relationship text to text with trailing spaces

Yes, it's somewhat unexpected that the modeling engine would automatically trim values. However, the trailing spaces are still recognized in the query editor, so I would recommend adding a custom column there and simply concatenating a random character. E.g. "AB123" -> "AB123_" & "AB123 " -> "AB123 _"

image.png

purnima13 Regular Visitor
Regular Visitor

Re: Relationship text to text with trailing spaces

@PattemManohar Thank you. This is indeed a good idea.
Is there any way for it not to impact perfomance in the relationship?
A hexadecimal would be a text according to power BI and large text field have poor performance.

v-yulgu-msft Super Contributor
Super Contributor

Re: Relationship text to text with trailing spaces

Hi @purnima13,

 

I have e-mailed to Product Team to consult with them about this problem. Will update my reply here once I get any response.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yulgu-msft Super Contributor
Super Contributor

Re: Relationship text to text with trailing spaces

Hi @purnima13,

 

I got below response from PG:

When strings are imported into AS Engine, the trailing spaces are trimmed.

 

It looks like currently we are not able to avoid this behavior. The workaround is to replace the trailing space with other type of character.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 40 members 1,027 guests