Forum Discussion
Formatting Column and Data
In a data file that I have brought into powerbi, one particular column/field is 98% made up of 5 or 6 numbers, and the balance is made up of a combination of numbers and letters, like this - DS2-1328-000002.
I then tried to create a relationship between two files, using the same field as the common denominator, and I get an error message. I tried to format the column so both numbers and the number / letter combination are shown properly, but i cant seem to figure that out.
Any ideas ?
6 Replies
That field is necessarily a text field. Which is okay unto itself. But then to join to the other table it must be a text type field as well - so this data type mismatch could be the culprit.
In SQL we can do a many to many join but in a Power BI app I believe for the join you want 1 table that has only unique values. So if in both of your two tables the values do repeat - then you need to create a reference table. Then you join both tables to that.
Hi iraf,
According to your description, one table contained a particular field which had numbers, and combination of numbers and letters (DS2-1328-000002). When you used the same field on two tables to build relationship, there was a error throws out, right?
In your scenario, this particular field should be Text data type. Does the same field mean this particular column? If that is a case, the same field in other table needs to be Text also mentioned by CahabaData. If not, how about other fields? Can you share the detail error message and some screenshots about data tables for our analysis?
By the way, to create relationship, you can take a look at this article: Create and manage relationships in Power BI Desktop. About changing data type for a column, see: Data types in Power BI Desktop.
Best Regards,
Qiuyun YuThank you for both responses.
So I followed both steps to make sure that both columns are in text data format. Wen I tried to create the relationship again, I got an error message referencing unique values. I have no idea what that menas at this point, and do not know how to resolve. I have attached a screenshot of that error message.
Thank you again.
if in both of your two tables the values do repeat - then you need to create a reference table. Then you join both tables to that.
