Guide to Power Query Context Menus

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

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.

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

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

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

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

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

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

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 r ibbon)	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 r ibbon )	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 r ibbon )	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 r ibbon )	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 i n Query Editor r ibbon )	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 i n Query Editor r ibbon )	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

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

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 i n Query Editor r ibbon )	Remove the selected columns from view and further use.
Remove Other Columns (Available i n Query Editor r ibbon)	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 i n Query Editor r ibbon)	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 i n Query Editor r ibbon)	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 i n Query Editor r ibbon)	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

Table context menu

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

Action	Description
Use First Row as Headers (Available i n Query Editor r ibbon)	Promote the values in the first row of a table to column headers.
Remove Duplicates (Available i n Query Editor r ibbon)	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 i n Query Editor r ibbon)	Remove rows containing errors in the currently selected columns.
Insert Column (Available i n Query Editor r ibbon)	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 i n Query Editor r ibbon)	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 i n Query Editor r ibbon)	Specify a range of rows starting a First row extending by Number of rows to keep in the table.
Remove Top Rows (Available i n Query Editor r ibbon)	Remove top n rows from the table.
Remove Alternate Rows (Available i n Query Editor r ibbon)	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 i n Query Editor r ibbon)	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 i n Query Editor r ibbon)	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

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

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

Microsoft Office Tutorials

Thursday, March 9, 2017