Formatting Column and Data

[iraf]

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 ?

[v-qiuyu-msft]

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 Yu

[iraf]

Thank 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.

[CahabaData]

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.

[iraf]

i am having trouble finding any knowlege base articles on how to set up a reference table. can you direct me in the rigt direction ?

[CahabaData]

sorry I think I just invented that term and it is not official.....

a table that has all the values - but never a duplicate.  The table could be a single column - though let's say a product table might have the official product # but then also a decriptor maybe even a unit price.

you can import it, or build it manually, or derive it by appending other tables' columns to a new table and then taking a DISTINCT/VALUES of it.

[CahabaData]

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.