This post may contain affiliate links. Please read my disclosure for more info.

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 files

It may be tempting to just read the text below, but you will retain the information much better if you work along with the examples.

Subscribers can download the files from the Downloads section.

Excel Downloads

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.

Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

Leave a Reply

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