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.
Get Data from OneDrive or SharePoint with Power Query
Kristy Thai
Hi – I keep getting an error on my () negative amount in the Power Query. I tried the Locale method, but I imported the data set from a PDF file, so I cannot view the format of the amount. Please help.
Mynda Treacy
Hi Kristy,
Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
Jessica
I’m trying to change the date format, without using the ‘locale’ feature. Is their a way to do that within the M code?
Right now my date is displaying 9/1/2021 – but I want the month and day to always be two digits (as 09/01/2021) and the year to always be 4 digits. It should display MM/dd/yyyy – can’t I do this with a formula as a new custom column? I need to do this in Power Query Editor (not Power Query BI).
It would be swell to do this as one formula, not have to split the date into three columns to force a leading 0.
Mynda Treacy
Hi Jessica,
Power Query isn’t the place to worry about how the dates are displayed. This formatting you describe is done in Excel once the data is loaded. If you force a date to appear as mm/dd/yyyy in Power Query you would have to convert the dates to text, and you should never do that to a date because then it becomes unusable in calculations and is not able to be grouped in filters or PivotTables. My advice is to simply make sure the dates are correctly formatted for your locale, and then use the Excel cell formatting to get the desired look of mm/dd/yyyy once you load the data.
Mynda
Will Ryu
Thank you and Phil for the no-nonsense and to-the-point no-fluff videos delivered in that calm manner. The videos helped me take the first steps towards the world of Power Q magic and continue to inspire me to explore further.
Is there a way to display large numbers in a non-scientific format – 1234567891011 displays as 1E12. I know it can be formatted as text to show all the digits. Similarly with the commas – 1,234,567,891,011 or even the #,,, format? And retain that format.
Thanks
Mynda Treacy
Hi Will,
Use a custom number format with 13 zeros.
Mynda
Will
Thanks Mynda
However, in my version of PowerQuery Editor, I am unable to choose a custom number format. I don’t and can’t have PowerBI loaded.
Mynda Treacy
You don’t apply the custom number format in Power Query. You do it in the Excel Worksheet. Power Query is not for formatting. That’s something that is done in the worksheet cells.
Will
Thanks for the clarification. Yes, I do know how to do that in Excel.
It’s just that the larger numbers cant be read in PowerQuery as they show up as 1E12 etc. which is why I formatting them as text, which allows me to see the entire number.
After loading to Excel, I convert them back to number
Jean-Marie Lambert
Hello,
Thanks for your mail.
Why do not use in Power Query Editor : Query Options and modify Regional Settins ?.
Best regards,
Jean-Marie
https://perso.unamur.be/~jmlamber/
Mynda Treacy
Great question, Jean-Marie. You can do that if you’re confident that all data you import is from the locale setting you choose, but often people are importing data from a foreign locale as well as their own, so this approach would also end up with errors in your data.
David James Bonin
Mynda,
On the related topic of dates with VBA, I discovered an odd quirk with Excel and Windows 10.
I set my PC to use a date format of yyyy-mm-dd. Why? Because it visually collates better in Windows Explorer. My PC displays today’s date as 2021-03-25.
When importing / exporting data from / to our mainframe SAP database using Visual Basic, I needed to convert dates from Excel’s numeric date values to string values in the format of mm/dd/yyyy.
I figured I could use something like this in VBA to create a string value of today’s date:
Format( Date, “mm/dd/yyyy” )
That didn’t work. VBA creates a string value in the format of mm-dd-yyyy. Windows and Excel ignored my explicit instructions about the delimiters.
I found I needed to use something like this instead:
Format( Date, “mm” ) & “/” & Format( Date, “dd” ) & “/” & Format( Date, “yyyy” )
That worked.
Mynda Treacy
Interesting, David. I’d have just used a custom number format on the cell so that the dates were still proper date serial numbers and could be used in formulas.
Claus Scheyda
Thanks for the information on using “change type with locale”. Perhaps it would be helpful to mention that the locale can be changed (at least in Excel Power Query) for all workbook queries at once. This is helpful when importing lots of data with a different (but identical) locale.
On Data ribbon select Get Data > Query Options (exact position varies by Excel version); then Current Workbook > Regional Settings > Locale. This applies to all existing and future queries in the specific workbook.
Mynda Treacy
Yes, you can do that if you’re certain all data being imported is from the locale you change it to. Be careful though, especially if you’re sharing that file with others who might not be aware that you’ve applied that setting.
jomili
I have a similar but different problem. My dates should be m/d/yyyy. They came in as 1/2/2021 (internal value 44,198.07 ), formatted as 2/1/2021 (in column C). I need them to actually BE 2/1/21 (internal value 44,228.07). I can’t get PQ to do that for me. Only solution I’ve found is the formula =–IF(N(C13),TEXT(C13,”d/m/yy h:m”),C13). Is there a PQ method that would do this? I can send a sample file if it helps.
Mynda Treacy
Yes, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
jomili
Mynda,
It would be nice if you included a sample data file so users like me could work the steps.
Mynda Treacy
It’s a bit tricky to provide a source file in a different locale to my own as my system creates the files for my locale, which is dd/mm/yyyy. If you’re using a mm/dd/yyyy format then any file you download from our site will contain data in a dd/mm/yyyy format that you can test. For example, the Power BI sample files here: https://www.myonlinetraininghub.com/workbook-downloads