Edit query step settings

Whenever you add a query step it is inserted into the sequence of steps that follows the selected step. If you add a step anywhere other than at the end of the flow, verify that all the following steps function properly. You edit query steps in the Query Editor.

ShowLearn more about the Query Editor.

You can access the Query Editor from the Navigator pane when connecting to a data source, or at any time from the Workbook Queries pane or the contextual Query ribbon tab for an existing query. To learn how to load the Query Editor, see Power Query Quick Start.

 Note    When you insert or delete an intermediate step in a query you might potentially break a query. Power Query will display an Insert Step warning when you try to insert a new step.

I want to


Add a query step

There are two ways to add a query step to your query.

Use Query Settings

  1. In the Query Settings pane, in the APPLIED STEPS list, click a query step.
  2. After you complete a query step that reshapes your data, a query step is added below the selected query step. For more information about how reshape your data, see Shape data.

Use a formula

  1. If needed, in the Steps pane, click a query step. The query step is added below the selected query step.
  2. Click the formula icon (Formula )to the left of the formula bar.
    A new formula is created in the form = <nameOfTheStepToReference>
  3. Type in the new formula as = Class.Function(ReferenceStep[,otherparameters]).
    For example: You have a table with the column Gender and you want 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.")

Hidden columns

 Note    When you add a query step, an error might occur in subsequent steps. An error is produced if the new step alters fields, such as column names, that are used in any of the steps that follow the inserted step.

Top of Page Top of Page

Edit a step

There are two ways to edit an existing step.

Use Query Settings

  1. In the APPLIED STEPS list, click the step you want to edit.
  2. Click the gear (Gear ) icon or right click, and click Edit Settings.
  3. In the dialog box for the selected step, edit the query step values.
  4. Click OK.

Use the formula bar

  1. In the APPLIED STEPS list, click the step you want to edit.
  2. In the formula bar, change the values in the formula as needed. For more information about Power Query formulas, see Learn about Power Query Formulas.
  3. In the Query Editor dialog, click Refresh.

Top of Page Top of Page

Delete a step

  1. Click the (Delete step ) icon to the left of the step you want deleted or
  2. Right click the step, and click Delete or Delete Until End.

Top of Page Top of Page

Reorder a step

You may move a step up or down in the Steps pane. Changing a step’s position in the Steps pane can make one or more steps fail. Be sure to verify that all steps are working properly after you finish the reorder.

To move a step up or down the list of steps in the Steps pane

  1. In the Steps pane, right click the step you want to move.
  2. From the context menu, click either Move Up or Move Down to move the selected step one position up or down the list.

 Note    To move a step multiple positions, repeat these steps. When a step is at the top or bottom of the list one of the move options is grayed out.

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Excel 2010