- Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010.
- Note: Power Query is also known as Get & Transform in Excel 2016. Information stated here corresponds to both. To learn more, see Get & Transform in Excel 2016.
- Note: For a short video on how to present Query Editor, refer to the end of this post.
- Note: Once you import or erase an intermediate step in a query, you may run the risk of corrupting a query. Power Query will indicate an Insert Step warning after you attempt to add a new step.
This post will teach you various ways of how to edit query step settings (Power Query). These ways comprise the following:
Any time you append a query step in Power Query, it integrates itself into the sequence of steps that comes after the chosen step. Should you insert a step anywhere else apart from the flow’s endpoint then you must confirm that every following step operates correctly.
The illustrated image presents the Query Editor alongside the Query Settings pane on the right side of the window. Query Editor is the location where the production, amendment, and refinement of Power Query queries occur.
Add a query step
There are two approaches to adding a query step to your query.
Use Query Settings
- The first way is by selecting a query step. Do this in the Query Settings pane, within the APPLIED STEPS list.
- Secondly, once you finish a query step that reshapes your data, a query step is inserted underneath the chosen query step. For further information about how reshape your data, see Shape data.
Use a formula
- If essential, click a query step in the Steps pane. The query step is placed below the specified query step.
- Select the formula icon ( ) to the left of the formula bar.
A new formula appears in the form = <nameOfTheStepToReference>
- Enter the new formula as = Class.Function(ReferenceStep[,otherparameters]).
For instance: You have a table with the column Gender and you wish to add a column with the value “Ms.” or “Mr.”, depending on the person’s gender. The formula would be = Table.AddColumn(<ReferencedStep>, “Prefix”, each if [Gender] = “F” then “Ms.” else “Mr.”)
Note: After you add a query step, an error may arise in subsequent steps. An error will generate if the new step modifies fields, like column names, that are referenced in any of the steps that trail the added step.
Edit a step
You can edit a current step in two ways.
Use Query Settings
- In the APPLIED STEPS list, confirm your desired step you seek to adjust.
- Press the gear ( ) icon or right-click, then click Edit Settings.
- Next, amend the query step values. This will be done in the dialogue box for the particular step.
- Finally, select OK.
Use the formula bar
- Firstly, choose your preferred step to edit, in the APPLIED STEPS list.
- Secondly, edit the formula values as necessary in the formula bar. To find out more about Power Query formulas and how to edit query step settings, read Learn about Power Query Formulas.
- Lastly, select Refresh in the Query Editor dialogue.
Delete a step
- Choose the ( ) icon to the left of your target step to be erased or;
- Alternatively, right-click the step, then press Delete or Delete Until End.
Reorder a step
You might reposition a step up or down in the Steps pane. Altering a step’s position in the Steps pane can cause one or several steps to fail. Make sure you verify that every step is functioning properly when you complete the reorder.
To move a step up or down the list of steps in the Steps pane:
- Firstly, right-click your desired step to rearrange in the Steps pane.
- After this, select either Move Up or Move Down to move the chosen step one position up or down the list. Do this from the context menu.
Note: To move a step numerous positions, repeat these steps. Once a step is at the top or bottom of the list, one of the move options is greyed out.
Note: The Query Editor only emerges when you load, edit, or create a new query using Power Query. The displayed video depicts the Query Editor window coming into view after editing a query from an Excel workbook. To view the Query Editor excluding loading or modifying a current workbook query, follow this process:
From the Get External Data section in the Power Query ribbon tab, pick From Other Sources > Blank Query. The video beneath presents one way to display the Query Editor.