Guide to Power Query Context Menus

Context menus are specific to an element, such as a table cell, in the editor pane. A context menu defines actions to take on the selected element or elements.

In this article


Cell context menu

To apply an action to a cell, right-click any cell. Each cell type has a context menu.

Number or Date/Time type   

Action Description

Number Filters

Filters a table based on an operator:

  • Equals
  • Does Not Equal
  • Greater Than
  • Greater Than Or Equal To
  • Less Than
  • Less Than Or Equal To

Replace Values

Replace one value with another value in the selected columns. To replace number or Date/Time values, see Replace number or Date/Time values.
Drill Down Navigate to the contents of the cell. This adds a new step to the current query to perform navigation; it can be undone by removing this step in the Steps pane.
Add As New Query Create a new query with the contents of this cell as the query values. The name of the new query is the same as the column header of the selected cell.

Replace number or Date/Time values

  1. Right-click a number or Date/Time cell.
  2. Click Replace Values.
  3. In the Replace Values dialog box, enter Value To Find and Replace With.
  4. Click OK.

Top of Page Top of Page

Text type

Action Description

Text Filters

Filters a table based on an operator:

  • Equals
  • Does Not Equal
  • Begins With
  • Ends With
  • Contains
  • Does Not Contain

Replace Values

Replace one value with another value in the selected columns. To replace text values, see Replace text values.
Drill Down Navigate to the contents of the cell. This adds a new step to the current query to perform navigation; it can be undone by removing this step in the Steps pane.
Add As New Query Create a new query with the contents of this cell as the query values. The name of the new query is the same as the column header of the selected cell.

Replace text values

  1. Right-click a text cell.
  2. Click Replace Values.
  3. In the Replace Values dialog box, enter Value To Find and Replace With.
  4. Click Match entire cell contents option to replace cells from the entire contents; otherwise, Replace Values replaces each Value To Find match.
  5. Click OK.

Top of Page Top of Page

Logical type

Action Description

Replace Values

Replace one value with another value in the selected columns. To replace logical values, see Replace logical values.
Drill Down Navigate to the contents of the cell. This adds a new step to the current query to perform navigation; it can be undone by removing this step in the Steps pane.
Add As New Query Create a new query with the contents of this cell as the query values. The name of the new query is the same as the column header of the selected cell.

Replace logical values

  1. Right-click a logical cell.
  2. Click Replace Values.
  3. In the Replace Values dialog box, enter Value To Find and Replace With.
  4. Click OK.

Top of Page Top of Page

Table or Link type

Action Description
Drill Down Navigate to the contents of the cell. This adds a new step to the current query to perform navigation; it can be undone by removing this step in the Steps pane.
Add As New Query Create a new query with the contents of this cell as the query values. The name of the new query is the same as the column header of the selected cell.

Column context menu

Right-click selected column headers to apply an action to all the cells in that column. Each column type has a context menu.

Single column selection      

Action Applies to type Description
Remove All types Remove the selected column from view and further use.

Remove Other Columns

All types Removes all non-selected columns from view and further use.

Use First Row as Headers

All types Each table header name is replaced by each cell value in the first row of the table.

Duplicate Column

All types Creates a duplicate of the selected column at the right side of the table. The name given to the new column is Copy of <column name>.

Split Column   

(Available in Query Editor ribbon)

Text type

A column of text can be split into multiple columns in two ways:

  • By Delimiter   
  • By Number of Characters

For more information about how to split a text column, see Split a column of text.

Remove Duplicates

(Available in Query Editor ribbon)

Text, Number, Date/Time,

Logical types

Remove all rows from the table where the values in the selected columns duplicate earlier values. The row with the first occurrence of a value set is not removed. For more information about how to remove duplicates, see Remove duplicates.

Replace Values

(Available in Query Editor ribbon)

Text, Number, Date/Time,

Logical types

Replace an item in the column with another value. For more information about how to replace values, see Cell context menu.

Change Type

(Available in Query Editor ribbon)

Text, Number, Date/Time,

Logical types

Disabled for Table, Link, and Entry types

Change the type of the selected column to a new type:

  • Logical
  • Number
  • Date
  • Date/Time
  • Date/Time/Timezone
  • Time
  • Duration
  • Binary
  • Text
  • Type With Culture. For more information about how to change a type with the culture of origin, see Change type with culture.

Transform

Text and Date/Time types

Disabled for all other types

For a text value, change the way that values in the column are rendered:

  • Lowercase
  • UPPERCASE
  • Capitalize Each Word
  • Trim
  • Clean
  • JSON
  • XML

For a Date/Time value, change the way values in the column are rendered:

  • Date
  • Time
  • Day
  • Month
  • Year
  • Day Of Week

Insert Column

All types

Inserts a new column after the last column of the table. The values in the new column are determined by selecting the type of column to insert:

  • Custom (you define the formula that calculates the new values)
  • Index

For more information about how to insert a column, see Insert a custom column into a table.

Group By

(Available in Query Editor ribbon)

Text, Number, Date/Time,

Logical types

Summarizes data by row values. For example, given the following columns in a table, State, City, Population you could:

  • Group by State and either count the number of cities in each state or sum the population of the cities to get the population of each state
  • Group by City and count the instances of city names.

For more information about how to group rows, see Group rows in a table.

Unpivot Columns

(Available in Query Editor ribbon)   

All types Transforms selected columns into attribute-value pairs. For more information about how to unpivot columns, see Unpivot columns.

Move

All types

Move the selected column to a different location in the table. Move the selected column:

  • Left
  • Right
  • To Beginning
  • To End

Alternatively, you can click then drag and drop columns to the left or right of the selected column.

Rename

All types Rename the selected column.

Drill Down

All types Navigate to the contents of the column. This adds a new step to the current query to perform this navigation; it can be undone by removing this step in the Steps pane.

Add a New Query

All types Create a new query with the contents of this column as the query values. This is done by referencing the original query in the new one. The name of the new query is the same as the column header of the selected column.

Change type with culture

  1. Right-click a selected column.
  2. Click Change Type > Type With Culture.
  3. In the Change Type with Culture dialog box, select a Data Type to change and select the Culture.
  4. Click OK.

Top of Page Top of Page

Multiple column selection

Select (Ctrl+Click or Shift+Click) two or more columns, then right-click any of the selected columns to apply an action from the multi-column context menu to the selected columns.

 Note    For some multiple column selections, some context menu items will be disabled.

Action Description

Remove Columns   

(Available in Query Editor ribbon)

Remove the selected columns from view and further use.

Remove Other Columns

(Available in Query Editor ribbon)

Removes all non-selected columns from view and further use.

Remove Duplicates

Remove all rows from the table where the values in the selected columns duplicate earlier values. The row with the first occurrence of a value set is not removed. For more information about how to remove duplicates, see Remove duplicates.

Replace Values

Replace an item in the column with another value. For more information about how to replace values, see Cell context menu.

Change Type

Change the type of the selected column to a new type:

  • Logical
  • Number
  • Date
  • Date/Time
  • Date/Time/Timezone
  • Time
  • Duration
  • Binary
  • Text
  • Type With Culture. For more information about how to change a type with the culture of origin, see Change type with culture.

Transform   

(Available in Query Editor ribbon)

For a text value, change the way that values in the column are rendered:

  • Lowercase
  • UPPERCASE
  • Capitalize Each Word
  • Trim
  • Clean
  • JSON
  • XML

For a Date/Time value, change the way values in the column are rendered:

  • Date
  • Time
  • Day
  • Month
  • Year
  • Day Of Week

Merge Column

Group By   

(Available in Query Editor ribbon)

Summarizes data by row values. For example, given the following columns in a table, State, City, Population you could:

  • Group by State and either count the number of cities in each state or sum the population of the cities to get the population of each state
  • Group by City and count the instances of city names.

For more information about how to group rows, see Group rows in a table.

Unpivot Columns   

(Available in Query Editor ribbon)

Transforms selected columns into attribute-value pairs. For information about how to unpivot columns, see Unpivot columns.

Move

Move the selected column to a different location in the table. Move the selected column:

  • Left
  • Right
  • To Beginning
  • To End

Alternatively, you can click then drag and drop columns to the left or right of the selected column.

Top of Page Top of Page

Table context menu

The table context menu, represented by the table icon (Table icon ), performs table-wide actions.

Action Description

Use First Row as Headers

(Available in Query Editor ribbon)

Promote the values in the first row of a table to column headers.

Remove Duplicates

(Available in Query Editor ribbon)

Removes all rows from a Power Query table where the values in the selected columns duplicate earlier values. The row with the first occurrence of a value set is not removed.

Remove Errors   

(Available in Query Editor ribbon)

Remove rows containing errors in the currently selected columns.

Insert Column

(Available in Query Editor ribbon)

Inserts a new column after the last column of the table. The values in the new column are determined by selecting the type of column to insert:

  • Custom (you define the formula that calculates the new values)
  • Index

For more information about how to insert a column, see Insert a custom column into a table.

Keep Top Rows

(Available in Query Editor ribbon)

Specify the top number of rows to keep in the table.

Keep Top 100 Rows

Keep the top 100 rows in the table.

Keep Range of Rows

(Available in Query Editor ribbon)

Specify a range of rows starting a First row extending by Number of rows to keep in the table.

Remove Top Rows   

(Available in Query Editor ribbon)

Remove top n rows from the table.

Remove Alternate Rows

(Available in Query Editor ribbon)

Remove alternate rows from the table stating at First row to remove specifying the Number of rows to remove and Number of rows to keep.

Merge

(Available in Query Editor ribbon)

In the Preview grid, create a new query from two existing queries. One query result contains all columns from a primary table, with one column serving as a single column containing a navigation link to a related table. For more information about how to merge queries, see Merge queries.

Append

(Available in Query Editor ribbon)

In the Preview grid, create a new query that contains all rows from a first query followed by all rows from a second query. For more information about how to append queries, see Append queries.

Top of Page Top of Page

Query step context menu

Right-click a query step to modify a query step and change a query step position.

Action Description
Edit Settings Edit the values in the function that defines the selected step.

Rename

Give this step a new name. This is helpful if you want the steps to have meaningful names or to differentiate multiple instances of the same operation – inserting a column for example.
Delete Remove this step from the stream. Note: this can create errors in following steps if following steps have a dependency upon this step.
Delete Until End Remove this and all following steps in this stream.
Move Up Moves the selected step one position closer to the beginning of the stream.
Move Down Move the selected step one position farther from the beginning of the stream.

Top of Page Top of Page

Online Search

In the Online Search pane, right-click a data source summary item.

Action Description
Go to Source Go to a source web page for a selected data source item.

Terms of Use

Go to a Terms of Use page for a selected data source item.

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Power BI for Office 365, Excel 2010