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.

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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous I hope you solved the problem since it was a few years ago.  For other people, in Power Query open the Advanced Editor and you will see a line at the top like this:

     

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Longitude (x)", type number}, {"Latitude (y)", type number}, {"Station Name", type text}, {"Climate ID", Int64.Type}, {"Date/Time", type datetime}, {"Year", Int64.Type}, .........

     

    In this case, the "Climate ID" column should be a text field.  So edit the line from {"Climate ID", Int64.Type} to {"Climate ID", type text} before you close and apply the query. This will force PowerBI to use the correct format when it brings the data into your model.

     

    Best of luck!