Excel Text Columns read as Number

[slej]

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.

[marcoselias]

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.

[jthomson]

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

[BetterCallFrank]

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

[ripstaur]

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? 

[slej]

It didn't work.   

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 · Accepted solution]

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!    Thanks.

[BetterCallFrank]

Great you found a solution 🙂

The way it works by default is that the data type of a column is guessed by looking at the first 200 rows - so indeed in your case numeric data type was the default.

By sorting the z* values up front, the data type went to text 🙂

Frank

[LMorman]

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]

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]

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]

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]

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]

@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!

[Anonymous]

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