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:
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:
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:
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:
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':
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:
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:
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.
