Forum Discussion
Excel Text Columns read as Number
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.
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.
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.
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
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?
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.
