Introduction
In this page, I am demonstrating how to correct an error in Power Query's Append function. This page follows on directly from the first page in this two page series.
The Error
In the original example, I had two columns with the word percent in them. Let's say that's an error or at least that I didn't want that. Instead, I changed the headings to Revenue % Change and Profits % Change. When I make those changes and refresh my Queries ... this happens:
That is, every Query that uses those columns reports back to say that it cannot find the columns that contain the word percent and that now include the symbol % instead. This affects all three of my Queries and the Append Query.
How to Solve the Problem?
Open the Query Editor and look at the error messages it gives:
Whilst the error messages are the same for the three base Queries, we have to repair them each in turn. To do that, read the message and understand it:
Click on the first Query ... for_2019 in my case and then click on the down arrow in the formula bar to see this:
The error message says, cannot find Revenue Percent Change and in the formula bar is shows Revenue Percent Change in the middle of the middle row ... this is what it USED to say. You can also see that it says Profits Percent Change at the start of the third row in the formula bar ... which is also what it USED to say.
All we do is change Percent to % in both cases and press Enter. And look at what happens immediately:
Repeat for the for_2018 and for_2017 Queries ... make sure you do it properly! When you have done that, notice that the errors are corrected and the warning triangle next to the Query names have all gone ... INCLUDING the Append Query error.
By correcting the three base Queries, we automatically correct the Append Query with no further input needed. Here is the corrected Append Query now and I have highlighted where the error triangles used to be:
Click Close and Load and then Refresh your Append Query on the worksheet and here you are, fully updated. Again, notice there are no errors and you can see the newly updated column headers.
This Works of All Errors
In case you are wondering, if you made just one error in a column heading, you only need to correct that one error and your Queries will update to reflect any changes you make. This means that one error or dozens of errors are all highlighted and corrected in the same way. Making changes and corrections in the formula bar in the Query Editor is often all you need to do!
You can download my Excel file here although it is essentially the same file that you downloaded from the first of these two pages.
Duncan Williamson
17th March 2020
No comments:
Post a Comment