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.
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
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
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?
