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