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?

Answer 1

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.

Answer 2

Show the Legacy Import for text files in

Options -> Data

Run the Legacy Import in

Data -> Get Data -> Legacy Wizards