Prevent loss of decimal separator when importing CSV in PowerBI

Question

I was creating a repro for a different PowerBI question, and crafted this example (UTF-8) CSV in Notepad:

IssueDate,Total,Location
2017-01-02,29.50,Detroit
2017-01-03,11.50,Boston
2017-01-04,13.50,Atlanta
2017-01-05,13.50,Detroit

When I load this into PowerBI as an external datasource I see:

File Origin = "1252: Western European (Windows)"
Delimiter = "Comma"
Data Type Detection = "Based on first 200 rows"

Here's a screenshot:

And after I finish:

The problem: my decimal separators have gone!

I've tried many different solutions:

Changing "File Origin" to UTF-8;
Changing the source to use semi-colons instead of commas for delimiters (even though in the real case I cannot influence the separator, this was just for testing);
All options for "Data Type Detection";
Playing around with the data type for the column in the Query Editor;
Meticulously going through all the "Transform" options in the Query Editor;
In response to a comment I've tried quoting my values with " marks but this didn't change anything;
In response to a comment I've carefully read through the Csv.Document(...) documentation (which you can use in the advanced query editor) but found no argument that affects decimal separators or culture settings.

None of this helps.

I'm almost afraid to ask... but does my Windows region ("Dutch") have an influence here? And if so: how can I go around that? Because the data might be refreshed and/or the report might be edited by others that have other regional settings on their PC.

How do you get PowerBI to respect the decimal separator in CSV numeric values?

It is quite possible your locale is the reason. Not sure this will help but you could try escaping "29.50" your values in the csv? Or playing around with the Csv.Document() Power Query method parameters? (reference) — user5226582
@user5226582 Thanks for your comments. I've investigated both options but they unfortunately didn't help. I'll update my question to include them. — Jeroen

user5226582 user5226582 · Accepted Answer · 2017-02-22T09:29:13

You could try changing locale setting in Options -> Current File -> Regional Settings -> Locale.

This setting is per report, so other users should be able to work with this report afterwards, regardless of their locale.

Prevent loss of decimal separator when importing CSV in PowerBI

2 Answers