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.
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.
The Queries & Connections window will open, simply double-click on the name of a query.
Queries & Connections – Right-click Edit
Within the Queries & Connections window, we can right-click on the query and select edit.
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.
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.
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.
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.
The queries list will open.
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
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.
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.
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.
The Keep Top Rows window will open. The query is currently set to show 5 customers, change this to 3, and click OK.
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.
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).
A new step has been added in the middle of the steps.
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.
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.
The new step has been added (see the screenshot below).
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.
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.
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.
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.
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.
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.
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:
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:
Click on the last step in the query. The data preview now looks like this.
The changes we made in the formula bar above have been reflected in our query.
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.#
- Import Data
- Data Refresh
- Edit Queries
- Close & Load Options
- Using Parameters
- Basic Transformations
- Combine / Append Queries
- Import All Files in a Folder
- List All the Files in a Folder and File Attributes
- Import Data from the Current Workbook
- Import Data from the Web
- Unpivot Data
- Unstacking Data in a Column
- Lookup Values using Merge
- Change the Source Data Location
- If Statements for Conditional Logic