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

Power Query – Edit Queries

Power Query - Edit Queries

In this part of the Power Query series, we will look at how to edit queries.  We’ve seen a small piece of this in a previous post, 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: Power Query – Example Files

All the examples in this post use Example 7 – Edit Queries.xlsx from the downloads.  It is an Excel workbook which already contains some basic queries.

Open the up the file and let’s get going.

Edit an existing query

As with many features of Power Query, there are multiple ways to access the same action.  Opening Power Query 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 really need to know one or two.

Open Power Query

Queries & Connections – Double click

The easiest method is to click Data -> Queries & Connections from the ribbon.

Data Queries and Connection

The Queries & Connections window will open, simply double-click on the name of a query.

Double Click Query

Queries & Connections – Right-click Edit

Within the Queries & Connections window, we can right-click on the query and select edit.

Right Click - Edit Query

Queries & Connections – Hover Edit

When hovering over a query (don’t click, just hover), Excel displays the query summary.  Click the Edit option at the bottom.

Hover - Edit Query

 Launch Power Query Editor

Another method is to open the Power Query editor without selecting a specific query.

Click Data -> Get Data -> Launch Power Query Editor.

Launch Power Query Editor

Query Tools ribbon

Just one more method for you to try, select any cell in query table.  A new Query ribbon will appear.  Click Query -> Edit.

Query - Edit

Selecting a Query to Edit

Once the Power Query editor opens, we can edit any query which exists in the workbook.  Expand the queries list by clicking on the arrow to the top left of the data preview.

Collapsed Query List

The queries list will open.

Collapsed Query List

Click on any individual query to activate it for viewing or editing.

Close & Load

In this series to date, we have used Close and Load only when creating queries.  We have only needed to load single queries into Excel.  But please be aware that Close & Load is applied to all the queries in the workbook.  So if you make changes to multiple queries, then updated with Close and Load, all the queries will be updated, not just the active query.

Close without saving changes

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

Discard and close

Alternatively, you could click the standard [X] close button (top right of the window).   There is a confirmation window to ensure we didn’t click it by mistake.

Do you want to keep changes

Edit the steps

Now that we are in Power Query and can move freely between queries, we are ready to start editing individual steps.  In this section, we will work with the example file to modify the existing transformations.

Use one of the methods above for opening Power Query and let’s get started editing some transformations.

Basic settings

As we have seen already in a previous post, the gear icon exists next to many of the steps.  Clicking it will reveal the options window for that specific step.

Select the Top Customers query, then click on the gear icon next to the Keep First Rows step.

Edit query gear icon

The Keep Top Rows window will open.  The query is currently set to show 5 customers, change this to 3, and click OK.

Keep Top Rows window

Adding, inserting, deleting and moving steps

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

Select Query - Insert Step

To insert a step, selected an existing step, then perform the transformations we want to apply next.

Click on the drop down of the Date column and selected only 31 January 2019 (depending on your local date format it may appear slightly different for you).

Insert Step - Filter Query

A new step has been added in the middle of the steps.

Insert Step - additional row added

Keep clicking through the steps to view the transformations; there should be no issues with this example.

When changing, moving, or inserting steps, it can cause problems later in the query, as the changes you make will not flow through correctly into the subsequent steps.  Let me show you.

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

Insert Step - Change column header name

A warning message is displayed.  Not all warning messages are bad; you get to choose if it is relevant to your scenario or not.  In this example, we are trying to identify errors, so click Insert.

Insert Step - Warning Message

The new step has been added (see the screenshot below).

Insert Stps - Renamed Columns

Now click on the Calculated End of Month step.  Oh dear… there is an error.  This is because that step is trying to find a column called Date, which is no longer there.

Insert Step - Error

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 above).

Now you get some choices.  Do you definitely need to insert that step at this point?  Should you delete and re-perform the affected steps?  Should you 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 which is best.

If you choose to delete all the remaining steps, right-click on the step and select Delete Until End.

Delete Until End

As this was just an illustration of the potential issues with changing steps, let’s delete the Renamed Columns step that we added, Clicking on the X next to the added step.

Delete single step

Moving steps

To move a step up or down there are options.   We can either right-click on the step and click Move Up or Move Down.

Move up move down

The other option is to click on a single step, hold the mouse button down and drag the step to a new location.

As you can imagine, moving steps has the same issues as inserting steps.

Where did my step go?

Power Query is smart, it tries to group similar actions together.

Select the SalesByDay query and ensure you have selected the last step, which is Removed Columns.  We will  now change the headers of each column:

  • Day Name change to Day – a new step has been added.
  • 31/01/2019 (or your local date equivalent) change 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.

Combined Steps - Formula Bar

Look at the Formula Bar (if you have it open), you will see that both the steps have been combined into one.

This only occurs where 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 in Formula Bar & Advanced Editor

OK, things are about to get serious – we are going to change the M code!  We can edit the code directly in two ways (1) Formula Bar (2) Advanced Editor.  As I prefer the Formula Bar method, we will start with that.

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

We might not be able to write M code from scratch, but that doesn’t mean we can’t edit it.  In the formula bar, change “Day of Week” for “Day Name”.  Look at the  data preview window; it’s now sorted alphabetically based on the Day Name.

Then 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:

Formula Bar after change

Click on the last step in the query.  The data preview now looks like this.

Updated Preview

The changes we made in the formula bar above have been reflected in our query.

Advanced Editor

We could make those same changes in the Advanced Editor.  Click on Home -> Advanced Editor.

The Advanced Editor will show every step, we must identify the relevant line, then we can make the same changes.

Load into Excel

Finally, let’s close this out by clicking Close and Load.  The changes we’ve made will be loaded in the Tables on the worksheet.

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:

6 thoughts on “Power Query – Edit Queries

  1. Khalid El-Bayati says:

    I have just installed the trail version for microsoft 365 .
    I have noticed that the data “From Web” and “Get Data From Web” changed and it does not allow browsing to get to the required page with the required data which some time is password protected.. While Excel 2016 allow to brows to the required page and enter the password. and then brows to find the required table with the required date that can be scrabbed or downloaded toa worksheet.

    • Excel Off The Grid says:

      The Legacy Data Import tools are still available.
      Click: File -> Options -> Data -> Legacy data import wizards.
      Checked the legacy wizards you want to display, then click OK.

      The Legacy Wizards are now available in Data -> Get Data -> Legacy Wizards.

  2. George says:

    This is great. Wish I had found it a couple of weeks ago.
    I’ve got you all bookmarked up.
    I’m a beginner so I need this kind of detail.
    Thank You

    • Excel Off The Grid says:

      Thanks George – I’m glad your learning Power Query… if you invest the time, it will be well worth it 🙂

  3. Adam says:

    Thanks for the article!

    I find that if I have created a Query, ,added it to my Data Model, and added Measures in Power Pivot that are attached to that ‘table’ and reference that ‘table’ – if I later want to rename my Query, it breaks my measures and/or deletes them.

    Is there a way to rename a Query after you’ve added it to the Data Model and built relationships & measures, without breaking those relationships and/or measures?

    • Excel Off The Grid says:

      Hi Adam – Once you’ve used a query in a Data Model, I don’t believe that at present there is an easy way to rename it so that it all updates correctly into PowerPivot. You will need to rebuild your measures and relationships

Leave a Reply

Your email address will not be published. Required fields are marked *