Friday, May 1, 2020

Home Power Query 4 Ways to Fix Date Errors in Power Query + Locale & Regional Settings

4 Ways to Fix Date Errors in Power Query + Locale & Regional Settings

May 01, 2020 Power Query,

Power Query is an amazing data automation tool that helps us clean up and import data into Excel. Just like Excel, it has its quirks.

One of those quirks is date data types. If you are working with data that contains dates that are not in your local date format, then you will likely encounter some errors.

So this week’s post and video cover everything you need to know about the date settings in Power Query and how to fix data type errors.

4 Ways to Fix Date Errors in Power Query + Locale & Regional Settings

Bottom Line: Learn 4 different ways to fix date data type errors in Power Query, including with locale, regional settings, and custom formulas with Column From Examples.

Skill Level: Intermediate

Download the Excel File

The file that I use in the video can be downloaded here if you want to practice these tips on your own.

Power Query Date Locale.xlsx (120.4 KB)

Fixing Date Errors in Power Query

Sometimes in Power Query, when you attempt to format data as a date, you will receive error messages. This is because Power Query is unable to recognize the data. The most common occurrence for this is when the original format of the date is from a different region.

The U.S. uses the MM/DD/YYYY format for dates, while most of the rest of the world uses DD/MM/YYYY. This can create issues with date conversions in Excel and Power Query.

There are four different ways to address this issue of date errors.

1. Locale in Data Type Menu

The first way to fix a date error that stems from location differences is to choose the Using Locale setting in the Data Type drop-down menu for whichever column/query has the errors.

Click the Data Type box in the top-left corner of the column that contains the dates, then choose Using Locale…
This will open the Change Type with Locale window. Here you can choose Date as the Data Type, and then for the Locale field you can choose the location where the data originated.
Press OK to apply to change the data type to a date with the proper formatting.

This formatting will need to be done on each query that returns errors for date columns. If you are working with a query that has several date columns or a file with several queries, then you might want to use the next tip instead.

2. Locale in Regional Settings

You can also change the regional settings for your entire file. To do this:

Go to the File menu and select Options and settings, then Query Options.
This brings up the Query Options window. Select Regional Settings, and then select the Locale for where the data originated from.
Press OK to save the changes.

For any queries in this file that have particularities to a region, now the formatting will show correctly when you change the data type of a column to Date. You do NOT have to use the Using Locale option explained in #1 above.

Note: This setting stays with the file. So if you share the file with someone from another country they won't have to change the locale to match the source data. If they add new queries with dates in their local format, then they will need to change this or use the Using Locale option (#1 above).

3. Operating System Regional Settings

If you are looking to apply regional settings to more than just one file at a time, you can change the region settings on your operating system.

An example of this might be if you work remotely from one country, but the files you deal with each day come from a company in another country. Instead of changing the regional settings for each individual file, you're essentially telling your computer that you are located in the other country. That way, it will display all of your data in the formatting of that region/locality.

To change your settings in Windows:

Go to the Start menu and begin typing the word “regional.” This will open the Settings window and navigate to the Region settings.
In the Regional format drop-down you can choose the location/format that most of your data comes from.
You might need to restart Excel for changes to take effect.

Change region settings for windows operating system — *Click to enlarge*

Keep in mind that changing this setting could have implications in other applications besides Excel. For example, your system clock might convert to 24-hour (military) time formatting, etc.

4. Custom Formula with Column From Examples

This last fix is for dates that are oddly formatted. For example, it's not just a matter of American vs. British preferences in formatting, but maybe you exported data from a system that spit out the data in a funky way.

If that's the case, you can use the Column From Examples feature in Power Query to do a little magic. This feature essentially does pattern recognition based on examples of how you would change the dates from the current format to your desired format.

To use the example from my video, if my dates are currently formatted as [25.11–2018, 29.5–2018, and 7.5–2018], I can tell Power Query that those dates should be [11/25/2018, 5/29/2018, and 5/7/2018]. From there, the Columns From Examples feature will guess the remainder of the data because it can pick out the pattern in my changes.

To use the Column From Examples feature:

Go to the Add Column tab in the Query Editor and select the Columns From Examples drop-down.
Assuming that you already have the column you want to change selected, you can click on From Selection. If you choose From All Columns, Power Query will include all of the columns in it's evaluation of what you type next. We only want to evaluate the date column for this example.
This will open up a new column, and that's where you will type in the date using the correct formatting. After two or three entries are typed in, Power Query will start to guess at the correct formatting for the remainder of the data. Once you can tell that it's correct, you can click on OK.
The new column with the dates is added to the end of the table.
Change the data type of the column to Date and rename the column.