Power Query: Steps to Take – Part 3

Welcome to our Power Query blog.  This week, I continue with my worked example by transforming dates.

The tent business has a new administrative assistant, who used to work in the United States.  George has provided some information, but it’s not yet in a format I can use:

In Part 1, I extracted the data and created a Base query, and took a Reference copy which I transformed to only hold the quantity rows: Quantities:

Last time, I created another query for the date information.   This query is called Dates:

This week, I need to transform the data so that the dates are in the correct format for my region (United Kingdom).  This doesn’t look simple, as the data has been input in US format in a UK locale, and the results are not consistent:

But guess what: it may be done in one step!  The key is locale.  I select all my date columns by holding down CTRL and clicking on each header.  Then I right-click:

In the ‘Change Type’ options, ignoring the Date related choices, at the bottom there is an option called ‘Using Locale…’.  This presents me with a dialog:

I need to change the Locale to ‘English (United States)’, or any other country using the same format as the US.  The data type is slightly trickier.  If I hadn’t changed the data type of week 1 to Text, I could use ‘Date’, but since I have, I have to convert them to ‘Date/Time’ first, otherwise I will get errors in week 1.

I click OK, and all my dates have been converted.  

Next time, I will put Quantities and Dates back together to get a list of dates and quantities.

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