Forum Discussion

iraf's avatar
iraf
Frequent Visitor
9 years ago

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...
Need Help
v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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's avatar
iraf
Frequent Visitor
9 years ago

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's avatar
    CahabaData
    Memorable Member
    9 years ago

    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's avatar
      iraf
      Frequent Visitor
      9 years ago

      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's avatar
        CahabaData
        Memorable Member
        9 years ago

        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.