Power Query: Dating Options

Welcome to our Power Query blog. This week I look at how to deal with dates in a variety of formats.

I have some dates in various formats in Excel that I am going to upload to Power Query.

I upload the data to Power Query using ‘From Table / Range’ in the ‘Get & Transform Data’ section of the Data tab.

I choose ‘My table has headers' and upload my data.

The Power Query engine has tried to make sense of the dates.  The M code applied to change the types of the columns is:

This means that it has recognised UK(Day/Month/Year) and Year/Month/Day as dates, but has left US(Month/Day/Year) and Day?Month?Year as type text.  This makes sense because I am based in the UK and that is my locale.  Interestingly, DayMonthYear has been treated as a whole number (Int64.Type).

I would like all the date columns to be transformed as dates, so I will look at each column in turn.

I will start by seeing what happens if I set the data type of date for US(Month/Day/Year).

I can do this on the Transform tab where there is a dropdown for ‘Data Type’

Since all that has happened so far is that Power Query has determined data types, I am invited to add this change to the existing step, which I do.

This is not what I want and could be confusing if I didn’t have days larger than 12. I need to help Power Query understand the format of this date. Since the date format is associated with a different locale, I can change the locale for this column.

If I click on the date icon next to the column name, I can access a dropdown list. Right at the bottom is an option to specify ‘Using Locale’. I choose this option.

I choose Data Type Date and Locale 'English (United States)'.

The data type and the display are now correct, and there are no errors. Next, I repeat the process for DayMonthYear, which is currently a whole number.

Changing to type Date gives me errors.  Since this is not in a recognised locale format, I need to reformat it using some M code.  I create a custom column from the ‘Add Column’ tab.

The M code I have used is:

This means I am converting the column to text and then taking the first two characters (the day), adding a “/”, then taking the next two characters (the month) and adding another “/”, and finally adding the last four characters (the year). 

I can now convert this to a date.

My final column is Day?Month?Year, which is a mixture of properly formatted dates and dates missing the separator. The rule of thumb here is that I have to cater for the most difficult case, so I need to remove the separators. I can do this by right clicking and using ‘Replace Values’.

This will leave me with all the data in the same format and I can use the same M code as before.  If I had a lot of dates in this format, I would create a function to do this.

The M code this time is:

Note that this time I have to use “#” and put the column name in quote marks (“”) so that the question mark (“?”) is not treated like a special character.

I change the new column to a date and rename the custom columns. My dates are now all formatted correctly.

Come back next time for more ways to use Power Query!