The topic of this post might be a none-issue for most Power BI users. Consider yourself lucky if this is the case. But depending on your system locale and the date and decimals format in your data, you might find yourself spending a frustrating amount of time on such basic things as ingesting numeric and date data in the correct format.
The Issue
What's the problem?
To load some csv or excel data, you click on 'Get Data', select your file, hit 'Load' and unfortunately realize that all columns with numeric data, e.g. 'Sales' in this case, don't match your input at all.
You check the original file and realize that 9702 is actually 97.02 in the data, and so on. In the same way, your date information might be messed up
How does that happen?
During data ingestion, Power BI performs some automatic steps, which is a very good thing most of the time. To display those steps, you don't click on 'Load', but on 'Transform Data' instead, right after selecting your data file. This way you get taken to the Power Query Editor. Luckily, on the pane to the right of this view you can track each step that has been applied in the actual order they have been applied. As you can see below, in one of the steps, column types have been changed, unfortunately in a wrong way though.
The Quick Fix
Obviously the decimal separator has not been recognized correctly, and this information then didn't survive the type change. You can verify this by 'stepping back in time' via selecting one of the steps above the one called 'Changed Type'. At the prior stages, the data still looks fine:
As the title of this blog suggests, the reason for this is that Power BI has interpreted this number through the lens of an incorrect locale. So a quick workaround would be to simply select a step where the data still looks fine, right-click the 'Sales' column in this case, select 'Change Type' and 'Using Locale...' and insert a step that converts the type of this column to a decimal number, but using a locale that fits the number format of the input numbers of this column (English for '10.56', German for '7,82', etc.). The same applies for columns with date or datetime information. The locale needs to fit the input format.
The Right Way
While this fixes the issue, it is certainly too much to bother with for every single numeric or date column every time. Luckily there is a better way:
- From the report or table view: in the data pane at the right, right-click the data table and hit 'Delete from model'.
- Hit "apply changes" if the yellow bar pops up under your tool bar.
- Open 'File' -> 'Options and settings' -> 'Options'.
- Under 'Current File', select 'Regional Settings'. From the dropdown menu on the right, choose whichever locale fits the format of the data.
- Hit OK, then save and reopen the file.
- Loading the data should now work as expected.
A Side Note
Just in case you were tempted: Never change the setting shown in the screenshot below, unless you have an actual reason to do so. This changes your expected DAX syntax locally. For example, instead of commas, you might then have to use semicolons to separate multiple arguments from one another within a function.
