Thursday, January 16, 2020

Guide to the power query ribbon

The Power Query and Query Editor ribbon tabs in Excel are the starting points for anything you do using Power Query.

Power Query ribbon tab

Ribbon control

Description

From Web

Import data from an intranet or extranet web page that you have access to.

From File

Import data from a file:

  • Excel

  • CSV

  • XML

  • Text

  • Folder

From Database

Import data from a database:

  • SQL Server

  • Microsoft Azure SQL

  • Access

  • Oracle

  • IBM DB2

  • MySQL

  • PostgreSQL

  • Sybase

  • Teradata

From Other Sources

Import data from another source:

  • SharePoint List

  • OData Feed

  • Microsoft Azure Marketplace

  • Hadoop File (HDFS)

  • Microsoft Azure HDInsight

  • Microsoft Azure Blob Storage

  • Microsoft Azure Table Storage

  • Active Directory

  • Microsoft Exchange

  • Facebook

  • Blank Query

From Table

Creates a new query that is linked to the selected Excel table. If the selected range is not part of a table, it will be converted into a table.

Merge

The Merge operation creates a new query from two existing queries. For more information about the Merge operation, see Combine multiple queries.

Append

The Append operation creates a new query that contains all rows from a first query followed by all rows from a second query. For more information about the Merge operation, see Combine multiple queries.

Manage Workbook

Displays the Workbook Queries pane so that you can manage your workbook queries.

Manage Shared Queries

Displays the Shared Queries pane so that you can manage your shared queries on your organization's Power BI site.

Locale

Determines the regional settings used to interpret numbers, dates, and time in imported text for this workbook.

Fast Combine

Controls whether Power Query combines data from two difference data sources securely.

Data Source Settings

Manage data source credentials, or sign in identities, for each data source connection you have used and data source privacy levels.

Update

Install the latest version of Power Query.

Options

Define options for your Power Query environment.

Help

Navigate to a comprehensive help experience.

Top of Page

QUERY ribbon tab

Note: The Query ribbon only appears when the currently selected workbook sheet has a query loaded.

Ribbon control

Description

Edit Query

Edit the selected query in the Query Editor.

Refresh

Refresh the selected query.

Duplicate

Create a duplicate query from the selected query.

Reference

Create a new query that references the selected query.

Delete

Delete the selected query.

Merge

The Merge operation creates a new query from two existing queries. For more information about the Merge operation, see Combine multiple queries.

Append

The Append operation creates a new query that contains all rows from a first query followed by all rows from a second query. For more information about the Merge operation, see Combine multiple queries.

Share

Share the selected query. For more information about how to share a query, see Share Queries.

Query Editor ribbon tab 

Query Editor ribbon

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query.

Ribbon control

Description

Home tab

Apply & Close

Apply your data query to import data into an Excel worksheet or load to the Data Model.

Discard & Close

Discard your data query.

Refresh

Refresh a query to import the latest data into a table without having to create the query again.

Remove Top Rows

Remove top n rows.

Remove Columns

Remove selected columns or Remove Other Columns from a query.

Remove Alternate Rows

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.

Remove Errors

Remove rows from a query with data errors.

Remove Duplicates

Removes 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.

Keep Top Rows

Keep top n rows.

Keep Range

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

Split Column

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.

Group By

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.

Replace Values

Replace one value with another value in the selected columns. 

Transform

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

Use First Row As Headers

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

Unpivot

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

Insert Custom Column

Inserts a new custom column after the last column of the table; wherein, you define the formula that calculates the new values.

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

Insert Index Column

Create a new index column starting at zero.

Duplicate Column

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>.

Merge

Merge the current query with another query in the current workbook.

Append

Append the current query with another query in the current workbook.

View tab

Query Settings

Displays the Query Settings pane where you can Filter & Shape a query, enable download, load to worksheet and load to data model.

Formula Bar

Top of Page

SEARCH ribbon tab 

Ribbon control

Description

Scope group

Filters your search by range of data sources:

  • All – Apply a search term for all scoped data sources.

  • My Shared – Range of data sources include my shared queries.

  • Organization (For Power Query 2.10) – Range of data sources include enterprise shared queries.

Refinegroup

Filters your search based on a query or table attribute. For example, to filter where Index is in the query name, the search filter is name:(Index).

Query or table attribute

Description

Name

Filter your search based on query and table names.

Description

Filter your search based on query and table description.

From

Filter your search to only show results from specific people or web pages.

Data Source

Filter your search to only include results from a particular data source

Date Range

Filter your search based on when the query was last modified. The date range options:

Today, Yesterday, The Week, Last Week, This Month, Last Month, This Year, Last Year

Column Names

Filter your search based on column names.

Certified

Only show results that are where the data source query is certified.

Recent Searches

List of your recent query searches.

Top of Page

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions