- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Get data from CSV file reads numeric columns as text
I'm doing:
Get Data -> Text/CSV
Data Type Detection: Based on entire dataset
The csv file was created in R using the command:
write.csv(dataframe, file = "./outputs/financials.csv", row.names = F)
There are a bunch of columns in the csv file that contain only numbers, but Power BI is still reading them in as text. It looks like PBI is converting large values into scientific notation, e.g.:
600000 becomes 6e+05
And therefore treating the whole column as text. In fact it only seems to treat numbers that are at the hundred thousand level, e.g. 100000 becomes 1e+05, 200000 becomes 2e+05, 300000 becomes 3e+05. All other values in the column are numbers.
A similar thing happens when I open the csv file in Excel, the 600000 becomes 65e+05 but only that cell is formatted as 'scientific' whereas the rest of the column is 'general'.
What the deuce is going on?
Edit: I know I can just go into the query editor and change the column type, but this is not a practical solution when there are over 90 columns that need to be corrected, every time the csv file is opened.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hmm, interesting. I was not able to reproduce with a file like:
ID,Val1,Val2,Val3,Val4
1,1000000,1000000,1000000,1000000
2,6000000,1000000,1000000,1000000
3,600000000,1000000,1000000,1000000
4,1000000,1000000,1000000,1000000
You can select multiple columns and then Change Type. You can also use Advanced Editor, line highlighted below.
let Source = Csv.Document(File.Contents("C:\temp\powerbi\largeNumbers.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Val1", Int64.Type}, {"Val2", Int64.Type}, {"Val3", Int64.Type}, {"Val4", Int64.Type}}) in #"Changed Type"
If the column names are consistent or in the same position (do it before promoting headers) then you could just copy and insert the line of code as appropriate. What version are you on? I seem to remember this sort of thing happening to me in the past but I can't reproduce on the current version.
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Greg. I have a feeling the problem is in the csv file that's created by R but I can't see anything obvious. It's fairly big, c. 90k rows and 90 columns.
I have, for now, created a custom function in the query editor that transforms all offending text columns to decimal. Not ideal, but at least I can now repeatedly update the csv file without incurring the wrong data types.
I'm on the following release:
December 2018
Product Version:
2.65.5313.841 (18.12) (x64)
OS Version:
Microsoft Windows NT 10.0.17134.0 (x64 en-US)
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
06-11-2024 12:46 PM | |||
06-12-2024 08:54 PM | |||
03-22-2024 10:22 AM | |||
Anonymous
| 05-23-2024 11:13 PM | ||
12-23-2020 06:40 AM |
User | Count |
---|---|
120 | |
73 | |
58 | |
57 | |
43 |
User | Count |
---|---|
179 | |
120 | |
82 | |
68 | |
56 |