Forum Discussion

slej's avatar
slej
Regular Visitor
9 years ago
Solved

Excel Text Columns read as Number

I'm trying to import an Excel file into BI Desktop. The first column in the spreadsheet is a TEXT column but when I try to load into my Power BI dashboard, it changes it to a NUMBER column. Consequent...
Need Help
  • slej's avatar
    slej
    9 years ago

    I got it to work!

     

    On a whim, I decided to sort the dealer number column in DESCENDING order.  This placed dealer numbers beginning with Z first instead of dealer number 13. 

     

    Apparently, PBI must make assumptions regarding the column type based not on the Excel file but on the first record(s) in the column when imported.  By changing the sort order so the alph/numeric records were first, everything loaded fine!  :smileyhappy:  Thanks.

LMorman's avatar
LMorman
Helper I
9 years ago

I am having the same issue.  I have sorted and saved the Excel file with the alpha characters first ( and formatted the columnas Text), but PowerBI is still reading the column as numbers and giving me an error message of the alpha records.

 

Any other options to try?

 

LMorman's avatar
LMorman
Helper I
9 years ago

Apparently changing the column type in the Query editor that pops up does not work, however going into the full Extract and changing the column type does work. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi

     

    I have the same issue. I have some customer numbers which are called 2750A and 2750B where the rest is 2750 and so forth.

    The query editor insist on reading this column as numbers even though i have changed it to text.

    As a result i get data error which is really frustrating.

    Does anybody have a solution for this type of problem?

     

    Thanks a lot

    Jannik

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi

     

    I have the same issue. I have some customer numbers which are called 2750A and 2750B where the rest is 2750 and so forth.

    The query editor insist on reading this column as numbers even though i have changed it to text.

    As a result i get data error which is really frustrating.

    Does anybody have a solution for this type of problem?

     

    Thanks a lot

    Jannik

    • slej's avatar
      slej
      Regular Visitor
      8 years ago

      Jannik,

       

      If you sort your column in DESCENDING order before importing into PB,  this should fix the problem. 

       

      Sorting in descending should place your non-numeric entries at the top.  Power BI makes assumptions of the data type based on the first so many records (regardless of how you actually code the field).  The descending sort will make it "see" the alpha-numeric records first so it will assume your data is text.  Thanks.

       

       -  slej

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thank you for you fast reply.

        My problem however is that the data comes from a database.

        It is an ODBC connection to our Microsoft Dynamics system where i link to ledger transactions.

        I have no way of sorting the data before it "arrives" in Power BI.