How to use Excel Power Query's best feature

We’ve covered various aspects of Excel Power Query the past two months, showing how it transforms data and can help with bank reconciliations.

Those are great, but I’ve saved the best for the last of our three-part Power Query series.

Power Query is a fantastic tool for transformations, but sometimes you run out of knowledge, experience, and/or coding ability to perform the changes you require. This is where a wonderful feature called Column From Examples comes in.

Consider the following data typed into Excel:

I want to change the dates to the format “Wednesday, December 25, 2019”. How on earth do I do this? If you have US regional settings, this field may not even recognise the values are dates, especially given the inverted commas (quotation marks). (If this sounds a bit familiar, you might recall that I referred to a Power Query option for date conversions while showing a non-Power Query conversion process in January.)

After creating a Table in Excel (Ctrl+T), I import the data into the Power Query Editor. I do what I know how to first. I know how to remove the quotation marks, by right-clicking on the field:

It’s easy to replace the punctuation with nothing.

This gives rise to data that is more manipulable.

However, depending upon your regional settings, it may not be as versatile as you think. For instance, if I were to change the data type to Date, you might get the following results:

This is because given my default regional settings, 25/12/19 represents the 12th of the 25th month in the year 2019, whatever that is (25 January 2021?). Certainly, Power Query is confused and provides the Error message for all but 1/1/20.

Retracting this step, I need to get cleverer. For those new to Power Query, it’s easy to miss the date type symbol’s functionalities. If I were to left-click on this symbol to the left of the field name (presently indicating the field is a text data type) …

I get the following shortcut menu, where I can select Using Locale….

This produces the Change Type with Locale dialog box, as shown in the screenshot below.

Here, I can again set the data type to Date, but I am also able to change the locale (ie, the regional setting) specifically for this field. For example (as shown), 29/03/2016 will be recognised as 29 March 2016 — which is exactly what I want. Therefore, I click OK.

That’s as far as I can get on brute force and ignorance:

At least I have dates now — it’s just I don’t know how to change it to the required format. There’s no button for it on the Ribbon!

And now for the good stuff

This is where I resort to Power Query’s star performer. If you forget everything else I have ever written, do remember the wonder that is Column From Examples, which is situated in the General grouping of the Add Column tab in the Power Query Editor, as shown:

Clicking on this reveals the following screen:

In the right-hand Column 1, I type exactly what I want, similar to the Flash Fill feature in Excel. Once I press Enter, the message above the tables advises me to enter more sample values.

Typing the same thing into the second row serves no purpose, so I skip the second row, type in the third row instead, and again press Enter:

Power Query guesses that the transformation I require (provided by M code) is:

Text.Combine({Date.ToText([Date], "dddd"), ", December ", Date.ToText([Date], "%d"), ", 2019"})

It’s not a bad guess, but it isn’t right, as it assumes the month will always be December and the year will always be 2019. Therefore, I give Power Query a further nudge in the right direction by putting one more row in for the final entry.

That’s more like it!:

Text.Combine({Date.ToText([Date], "dddd"), ", ", Date.ToText([Date], "MMMM"), " ", Date.ToText([Date], "%d"), ", ", Date.ToText([Date], "yyyy")})

This time, I can click OK, and I have my desired formatting.

If I delete the first field and rename the second field, I can then Close & Load back into Excel:

Suddenly, the impossible seems possible. If you can operate a mouse, know how to type, and can press Enter, then maybe you, too, can trivialise all of those manual, repetitive burdens you so look forward to each day at work. Just don’t let your boss find out …

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.