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
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 _"
@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.