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. Consequently, the import errors out on all of the records where the first column contains text and strips off leading zeros for those records that contain only numbers in that column (the leading zeros need to stay). How can I get Power BI to import this column as text instead of assuming it should be numeric? Thank you.
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.

14 Replies

  • marcoselias's avatar
    marcoselias
    Advocate V
    8 years ago

    You can also go to the Query Editor in Power BI, select the table coming from Excel file that is having the issue, and delete the "Change Type" action. This seems to leave the data types as originally defined in the Excel file. I had the same issue as you and this solved the problem.

  • BetterCallFrank's avatar
    BetterCallFrank
    Resolver IV
    9 years ago

    Hi,

     

    go to the query and edit it. In there you can click on the column that is supposed to be text and under the tab "Transform" select a text-data type.

     

    HTH

    • slej's avatar
      slej
      Regular Visitor
      9 years ago

      It didn't work.  :smileysad:  

       

      An example of what's happening:  in Excel the first column is called dlrNum and contains a 5-character dealer number.  Some numbers are all numeric with leading zeros (leading zeros need to be present in the dealer number) but most are alph/numeric - which is why the column is set as a text field. 

       

      A record in this column is:  00013.  When I get the data file to import into the dashboard, the Power BI preview window already shows the record as 13 and the column is now numeric.  Additionally, all records where the dealer number is alpha/numeric, now error out.  I tried the TRANSFORM and the CHANGE TYPE options and neither work.

       

      My other fields come in correctly - date fields as dates and other text fields as text.  Why would PBI do this?  Thanks.

       

       

       

       

      • slej's avatar
        slej
        Regular Visitor
        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.

    • ripstaur's avatar
      ripstaur
      Helper III
      5 years ago

      This doesn't really work. The problem comes in when you have something like Zip Codes or FIPS codes that have leading zeroes. If you change it to text, it will not add the leading zero back in. Why doesn't a microsoft app take data types from another microsoft app without changing them? 

       

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    I've had this happen but then just manually altered the changed type section and removed the part of it where it automatically tried to change something to a number