Forum Discussion
Locale settings should not affect CSV import
The output of the M code should not be affected by application's settings.
I disagree. The application settings govern how the input is interpreted. Under different interpretations, identical data means different things and the output should reflect proper meaning. Since a CSV file doesn't contain reliable locale metadata, there isn't a straightforward way for Power Query to magically know what interpretation you expect it to use unless you specify it in the code or in the application settings.
I'm not sure I understand your process, but, if I do, things may be working as they should.
Please correct any process steps I have assumed below.
- Your Windows Regional Settings are German, with comma as the decimal separator
- The CSV file is comma-separated data, with dot as the decimal separator.
- You import the CSV file, specifying en-US settings for the type conversion.
- Your imported file now shows comma as the decimal separator.
If the above is the case, then it is as expected.
Your system will process the data as decimal numbers.
If you want your system to use the dot as a decimal separator, then you will need to change your Windows Regional Settings
Thanks for looking into this so quickly. Actually, I could resolve the issue myself by using this article Set a locale or region for data (Power Query), particulary the paragraph "Use a non-default locale setting on a Change Type operation") and this video https://youtu.be/QKgS3hrrmvw?t=155. The explanations work well for Power Query in Power BI as well.
My most important learning was that the automatic conversion was the culprit. It turned the numbers into Int64.Type. This step needs to be deleted and replaced by a (Change Type – With Locale…) step, which generates a conversion to number with a culture parameter at the end.
= Table.TransformColumnTypes(#"Replaced Value", {{"SHAPE_LNG", type number},{"SHAPE_LNG", type number}}, "en-US") ,By use of the culture parameter, I get the decimals right in the model, even though they are rendered with commas (, through which I was confused).
For completeness I must express my apologies for ill-placing my question as a Power Query CSV issue. The actual task of rendering the map visual could be resolved in Power BI Desktop through the correct use of „Don’t summarize“.
In summary, through culture-specific type conversion in Power Query / M the coordinates from CSV worked as expected, despite being rendered with commas. They even work without type conversion as strings going into the map visual.
The rendering of decimals with commas in Power Query may be changed by Windows Regional Settings, but I caution this may not be desired because of possible side effects on other processes and legacy applications, and therefore only be a last resort.