Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Use the Power Query editor to update queries

Power Query - Edit Queries

This post looks at how to use the Power Query editor to edit existing queries in Excel. We’ve seen some of this in a previous post of this Power Query series, but now we’ll go a lot deeper.

Download the example file

I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download Icon
Download the file: 0091 Example 7 – Edit Queries.xlsx

All the examples in this post use 0091 Example 7 – Edit Queries.xlsx. So, open up the file, and let’s get going.

Open the Power Query editor

As with many features of Power Query, there are multiple ways to access the same action. Opening the Power Query editor to edit an existing query is no different; there are loads of ways to do it. I will briefly cover the most common below, though we only need to know one or two.

Queries & Connections

In my opinion, the easiest place to open the Power Query editor is from the Queries and Connections pane. Click Data > Queries & Connections from the ribbon.

Data - Queries and Connections

The Queries & Connections pane opens. Even within here are three ways to open the Power Query editor:

  1. Double-click on the name of a query.
  2. Right-click on the query and select edit.
  3. Hover over a query name (don’t click, just hover), Excel displays the query summary. Click the Edit option at the bottom.

3 ways of lanuching Power Query editor from the Queries and Connections pane

All 3 of these options will get you into the Power Query editor.

Launch Power Query editor

We can get directly into the Power Query editor from the ribbon; we don’t need to select any specific query. Click Data > Get Data > Launch Power Query Editor….

Using Data - Get Data - Launch Power Query editor

Query ribbon

Clicking on a Table containing a query (green striped format by default) will reveal the Query ribbon. Then, Click Query > Edit.

Query Edit from the Query Ribbon

Also, right-clicking on a Table and selecting Table > Edit Query can get to the query editor.

Edit query available from inside a Table

Inside the Power Query editor

Once the Power Query editor opens, we can edit any query in the workbook. If the queries pane on the left is closed, click on the > arrow to expand the list. Once the queries pane is open, click on any individual query to activate it for viewing or editing.

Select query to edit in the preview window

Edit transformations

Now that we’ve selected a query, we are ready to start editing. For the remainder of this post, we are working with the example file. We will to modify the existing from that file transformations.

Use one of the methods above for opening Power Query, then we can get started editing transformations.

Basic settings

The gear icon exists next to many of the steps within the applied steps list. Clicking the icon reveals the options window for that specific step.

Using the Example file, select the Top Customers query, then click on the gear icon next to the Keep First Rows step. The Keep Top Rows window opens. The query is currently set to show 5 customers, change this to 3, and click OK.

Woop, Woop! We have just edited the query.

Adding steps

The applied steps box is also used to insert, move and delete steps. Let’s take a look at that in this section.

Select the TopCustomers query and click on the step called Calculated End of Month. The data preview shows all the transformation steps up to the end of the current selection.

Now let’s insert a new step. Click on the filter drop-down of the Date column and select only 31 January 2019 (depending on your local date format it may appear slightly different for you). Then, click OK.

New steps added into existing query

A new step appears below the previously selected step.

Clicking through the remaining steps to view the transformations, there should be no issues with this specific example. However, this will not always be the case. When changing steps in the middle of a query, it can cause errors later on, as the changes we make may not flow through into the subsequent steps. 

Click on the Removed Columns step. Change the header of the Date column to Month End.

A warning message is displayed. Not all warning messages are bad; you get to choose if it is relevant to your scenario or not. We are trying to force an error in this example, so please click Insert.

Add new step which will cause error in Power Query editor

The new step is added.

Next, click on the Calculated End of Month step. Oh dear… there is an error. This is because the Calculated End of Month step is trying to find a column called Date, which is no longer exists (we renamed it to Month End Date). If you still have the Formula Bar open, you can see the reference to the Date column, which is no longer available (see the screenshot below).

Error message from changing steps

Now we have some choices. Do we need to insert that step at this point? Should we delete and re-perform the affected steps? Should we edit the M code of the remaining steps to replicate the change? Only you can make this decision.

Once you’ve been in this situation a few times, you’ll soon work out the best options. I recommend only deleting the remaining steps and re-performing the transformations as a last resort. With some practice and perseverance, you’ll learn how to fix the most common errors by using the formula bar or inserting additional steps.

Deleting steps

The example above illustrates the potential issues from editing existing steps; let’s delete the Renamed Columns step that we added. Click on the X next to the step to remove it.

Delete a transformation from the Applied Steps window

Deleting is easy, but be aware that this can cause errors too.

Moving steps

To move a step up or down, there are again more user-interface options:

  1. Right-click on the step and click Move Up or Move Down from the menu (see screenshot below)
  2. Click on a single step, hold the mouse button down and drag the step to a new location. This is my preferred option.

Right click menu in applied steps

As you can imagine, moving steps has the same issues as inserting steps; it can result in errors.

Where did my step go?

Power Query is smart; it groups similar actions together where it can.

Select the SalesByDay query and ensure you have selected the last step, which is Removed Columns1. Now, let us change the headers of each column:

  • Change Day Name to Day – a new step is added.
  • Change 31/01/2019 (or your local date equivalent) to Number of Sales – a new step is not added.

Look at the applied steps again… there is only one step added. Where did the second step go?

Take a closer look at the Formula Bar; you will see that both the steps have been combined into one.

Steps are combined where possible into single steps

This occurs when like-for-like steps are performed one after another. It certainly helps to keep the applied steps significantly smaller than they might be otherwise.

M code: Formula bar & Advanced Editor

OK, things are about to get serious – we are going to edit the M code! We can edit the code directly in two ways:

  • Formula Bar
  • Advanced Editor.

I prefer the Formula Bar method. So, let’s start there.

Formula bar

If the formula bar is not visible, we can turn it on by clicking View > Formula Bar from the ribbon.

Select the SalesByDay query and click on the Sorted Rows step.

The formula bar will show the following:

Formula Bar change

Even though might not be able to write M code from scratch, but that doesn’t mean we can’t edit the code with a bit of common sense. In the formula bar, change the text “Day of Week” to “Day Name”. If you look at the data preview window, the Day Name column is now sorted in ascending order.

Next, change the text which says Order.Ascending to Order.Descending. Check the preview window again, and notice the order has changed.

The final formula should look like this:

Change the query in the formula bar

See, it’s not so difficult to edit the M code after all 😀

Advanced Editor

The changes we saw above can also be applied in the Advanced Editor. Click on Home > Advanced Editor.

The Advanced Editor shows every step in the query. First, we need to identify the relevant code. To do this look down the left side of the code. In the applied steps the step was called Sort Rows in the Power Query editor it is the #”Sort Rows” steps we require.

We can then make the same changes as noted in the formula bar section above (see the highlighted section below).

Editing a query inside the Advanced Editor

Close and Load into Excel

Finally, let’s close this out by clicking Close and Load. The changes we’ve made are refreshed into the Tables on the worksheet.

An important point to note is that Close & Load does not just apply to the active query, but to all queries in the workbook. So, if we’ve edited multiple queries due the session, they will all be updated simultaneously.

Close without saving changes

After editing a query, we may decide we don’t want to load the changes into Excel. To discard the changes, click File > Discard and Close.

Close Editor without making changes

Alternatively,  we can click the [X] close button (top right of the window). A confirmation box ensures we don’t lose our changes by mistake. Clicking Discard returns us back to Excel without making any changes.

Wrap-up

This post shows us how to open and edit within the Power Query editor. While we have only made basic transformations, the methods shown apply to more advanced transformations too.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published.