Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I cant import decimal numbers from a csv file. The problematic fields in the csv file look like this: … ,0.702,… with a varying number of decimals. Column is by default detected as “Fixed decimal number”, but the decimal is ignored and the result is a whole number (702 for the above example).
I have tried pressing “Edit” before I load the csv file, and changing the data type to “Decimal number”. It doesn’t help, the result is the same.
If I press “detect data type” on the Transform tab, the result is “whole number” and only if use “Text” as data type can I see the decimal point. The problem is then of course that I am not importing numbers, which I need to do.
Does anyone have any idea on how to solve this seemingly trivial problem?
Many thanks in advance 🙂
Solved! Go to Solution.
Many thanks Marcel! Feeling a little embarassed now, but I just found an even simpler solution. There is a setting; "Archive - Options and settings - Options - Regional Settings - Locale" which I changed to English (United States). That did the trick. The data was now correctly interpreted and loaded.
I am located in Sweden and that apparently changed the way "." was interpreted in my CSV-file!
Thanks again for your help Marcel, have a great day!
When importing a csv file, you automatically get a step "Changed Type".
In this step,you need to add a culture that uses a decimal point instead of a decimal comma, e.g. (in the formula bar):
= Table.TransformColumnTypes(#"Promoted Headers",{{"Textfield", type text}, {"Number field", type number}, {"Integer field", type text}}, "en-US")
Hi Marcel,
Thanks for your reply! I am completely new to Power BI and dont understand where to put the code. I found a list of applied steps including the "Changed type" step, but I cant find any formula bar. Any chance you could elaborate a little for a beginner?
In the "View" menu, you can check "Formula Bar" which will display the formula bar with your formulas.
Many thanks Marcel! Feeling a little embarassed now, but I just found an even simpler solution. There is a setting; "Archive - Options and settings - Options - Regional Settings - Locale" which I changed to English (United States). That did the trick. The data was now correctly interpreted and loaded.
I am located in Sweden and that apparently changed the way "." was interpreted in my CSV-file!
Thanks again for your help Marcel, have a great day!
Another solution:
* use the Data->From Text/CSV option
* press the "Edit" button
* Look at the "Applied Steps" window (lower right); if there is a "Changed Type" step, then remove it (i.e. press on the "X" before this step)
* Close and load the Edit sheet
* thereafter you can replace "." with "," with a replace-all
This worked for me (Netherlands)
Groet, Robert Koffrie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |