Using Power Query to simplify your spreadsheets

Following on from Twenty Principles for Good Spreadsheet Practice, the Spreadsheet Competency Framework and the Financial Modelling Code, How to Review a Spreadsheet takes a very practical look at how the review process can help reduce spreadsheet risk.

One of the sections in the new report is headed Structural Review and one of the areas included is 'Complex Functionality'. The Complex Functionality list includes a range of Excel features such as arrays and PivotTables, and also includes: "answers derived from Power Query and Power Pivot". Of course, it's absolutely right to include Power Query as being potentially complex but this shouldn't suggest that solutions based on Power Query always need to be more complex than legacy Excel formulae. In fact, in many cases, Power Query can be a great deal simpler than the formula equivalent.

As an example, in an article from January 2020 in FM Magazine, a fellow Excel Community contributor, Liam Bastick, sought to show that, contrary to the opinion of the Internet, it was possible to convert US format dates to European format using an Excel formula. The article did make it clear that, although this could be done using a formula, there were alternatives, including the use of Power Query. This was the formula that Liam constructed:

As Liam acknowledged, Power Query can also convert US to UK dates. This is the equivalent formula in Power Query:

= Table.TransformColumnTypes(#"Replaced Value", {{"Date", type date}}, "en-US")

Of course, just comparing the formulae is only part of the story. Power Query works in a very different way to a cell formula and generally works with entire columns or tables, rather than individual cells. Accordingly, the Power Query version of the formula is entered as just one part of a wider process. First, our US dates, or the set of rows that includes our US date column, must be set up as a table. We then need to import that table into the Power Query editor by right-clicking anywhere in the table and choosing: 'Get Data from Sheet…' (formerly 'Get Data from Table/Range…'). Now we can create our date conversion step but, rather than having to type a formula into a cell, we use the Power Query editor interface to do all the work for us. We just click the Data Type icon in the Date column heading and choose Using Locale… from the list of options. In the 'Change Type with Locale' dialog we choose our required Data Type and source Locale from dropdown lists, and the editor creates our step for us:

It's worth highlighting the differences in the way that our 'formula' itself is created in Power Query compared to in an Excel cell. Although the amount of guidance available when entering Excel functions has improved version by version, you still need to know or find out which function to use and then type most of it in manually. In the Power Query editor, you can just let the user interface do all the work for you: choosing commands from the Ribbon tabs or column heading icons and then selecting from dropdowns or completing dialog prompts.

Once we have added our Changed Type step, we just use Close & Load to load our converted Table to an Excel worksheet.

Although the Power Query method is so much more automatic, and much quicker and simpler to create, than using an Excel formula, there is one enormous drawback. Queries have to be manually refreshed to immediately reflect changes in the original data whereas, by default, Excel formulae recalculate automatically.

If you can manage to live with such a significant change in the way that you use Excel, then there are some other advantages of using Power Query. Because Power Query works with Tables and columns, there is no need to copy formulae manually to new rows. In addition, and with relevance to where we started in terms of looking at the spreadsheet review process, all of our date results are created by a single step in the query process, rather than there being a separate formula for each individual calculation. This means we have a single step to review and check, rather than needing to check possibly thousands of individual cell formulae, any one of which could have been accidentally overwritten or changed.

Next time, we will look at some other spreadsheet review advantages of using Power Query rather than legacy Excel cell formulae.