ICAEW.com works better with JavaScript enabled.
Exclusive

Power Query and spreadsheet review

Author: Simon Hurst

Published: 08 Nov 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

The previous article considered how, far from being necessarily more complicated than legacy Excel, Power Query could simplify solutions to some spreadsheet problems. One of the significant ways in which Power Query makes things simpler is its potential to replace thousands of individual cell calculations with a single processing step that updates an entire column.

As the first article pointed out, this is not only easier to enter, but also easier to review. There are other advantages in using Power Query when it comes to reviewing a spreadsheet.

Another recent two-part article compared the use of Power Query to VBA. There are similarities between VBA and Power Query. Although Power Query steps are usually entered using the Power Query editor interface, the end result is a program written in 'M' code. Here, we have clicked on the Advanced Editor command in the Query group of the Home Ribbon tab, to display the underlying code created by the steps we have added:

Screenshot from Excel Power Query

Although both VBA and Power Query result in programming code, Power Query does have some significant advantages compared to VBA. As we have already mentioned, there is often no need to write any Power Query code directly - although it is only fair to point out that some VBA code can also be created without the need to type it in directly, by recording a series of actions as a macro in Excel. VBA also includes the ability to 'step through' a program. Displaying separate windows for the code and the spreadsheet allows the effect of each programming step to be reviewed. Useful as this can be when working with VBA, it's much easier to achieve in the Power Query editor. All we need to do in Power Query is to select a step, and the editor will show us our data as it is immediately after that step has been applied. In addition, we can often click on a 'gear' icon to the right of our step name to display the original dialog used to create and edit the step:

Screenshot from Excel Power Query

Of course, to review Power Query in this way does involve some knowledge of how Power Query and, in particular the Power Query editor, works. It is also possible for Power Query to show its workings in a way that is much harder to achieve using VBA or Excel functions. In our example of using Power Query to calculate an amount payable when different bands and rates are involved, we ended up with a single value, in the same way that an Excel formula would create a single result value in a cell. In order to review our calculation it might be useful to check one or more intermediate stages of that calculation.

The most obvious way to do this would be to right-click on our query in the query pane of the Power Query editor and duplicate it:

Screenshot from Excel Power Query

We could then remove all the steps that follow the intermediate stage that we wanted to review. The steps could either be deleted individually using the cross icon that appears to the left of the selected step or, if multiple steps need to be removed, by right-clicking on the first step to be removed and selecting: Delete Until End:

Screenshot from Excel Power Query

Although this method seems straightforward, it does suffer from one major drawback: it results in the first set of steps being duplicated in our full query and in our intermediate query. This could create a problem: were we to change any of these steps in our full query, without making the same change in our intermediate query, our 'workings' would no longer be consistent with our full calculation. Instead, and without needing to duplicate our query, we could right-click on the same step, but this time choose 'Extract Previous' rather than 'Delete Until End':

Screenshot from Excel Power Query

Extract Previous creates a new query that contains all the steps before our selected step. We have named this query 'PayableByBand'. As well as creating our new, intermediate query, Power Query also changes our existing query. It replaces all the extracted steps with a single 'Source' step that refers to our intermediate query:

Screenshot from Excel Power Query

This method preserves a single set of steps for our process, helping to ensure the consistency of our main query and our workings query. We could then tidy up our intermediate query to remove unwanted columns and change the column order, before using 'Close & Load to…' to load our workings to our worksheet. Note that, in this example, we have shown the calculation for all of our bands before filtering our rows to remove those where the Band start is above our Value and our Payable amount is therefore calculated as being negative:

Screenshot from Excel Power Query

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid
Topics
Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250