One of the most common issues I help people fix is data imported to Excel or Power BI using Power Query where the regional settings of the PC differ from that of the source data. Usually, the problem arises with dates. For example, data formatted dd/mm/yyyy imported on a PC with a mm/dd/yyyy date format. It can also be an issue with numeric data containing thousand separators or decimals where your region uses a comma in place of a period, or vice versa. Thankfully, it’s easily fixed using Change Type Using Locale with Power Query, but diagnosing the problem isn’t obvious.
Watch the Video
Change Type Using Locale with Power Query Steps
The problem often presents itself when the user is expecting data for a 12 month period, but they only have data for January. For example, below is the CSV file opened in Excel and you can see in the filter drop down there is data for every month:
Note: this dataset only contains data for the first date in each month, as you can see with January to March expanded to the day level of detail in the filter drop down above.
This data is stored in the CSV file in a dd/mm/yyyy date format.
When the data is imported to Excel, Power Pivot or Power BI on a PC with date format mm/dd/yyyy you get the following result where it appears there is only data for the first 12 days of January:
This is because Power Query assumes the date format in the CSV file is the same as your PC settings, in this case mm/dd/yyyy, and it imports the data accordingly.
If your files have data on days 13 to 31st of the month you may also notice that the query returns a lot of errors and only imports some of the data. This is because the data for dates 13 through to 31 of each month are discarded.
For example, consider the date 13 January 2020 formatted with dd/mm/yyyy looks like this 13/01/2020 in the CSV file. And when Power Query on a PC with date format mm/dd/yyyy imports it, it reads it as the 1st of the 13th month, 2020. Of course, there’s no 13th month, so this data ends up with errors which don’t get imported to your model.
So, now you know what to look out for, let’s look at how to fix it.
Fixing Power Query Locale Issues
Step 1: Go to the Power Query editor and remove the very first Changed Type step. It’s usually right after the Source step:
This might break the query, but don’t worry, it’s temporary.
Step 2: Go to the date columns (use CTRL to select multiple) and right-click > Change Type > Using Locale…
Step 3: in the Change Type with Locale dialog box choose Data Type ‘Date’ and in the Locale choose the locale of the dataset. For my CSV exmaple it’s English (Australia) which has a date format of dd/mm/yyyy:
You should now see the correct date format for your region.
Repeat for the other columns where required.
Step 4: Set the data types for the remaining columns via the icon in the top left of the column header:
Note: if your data also contains numeric values with thousand separators or decimals, you’ll also need to use the Change Type Using Locale… technique for those columns.
That’s it. Your data should be ready to close and load with no more errors.