Using Power Query in Office 365 Power BI: Creating, Sharing, and Managing Queries

In our previous article, “Power BI in Office 365: What You Need to Know” we positioned Power Query in the Power BI general scheme with the following image (see Figure 1).

Figure 1: Power BI Process

Any user willing to create a new report, or a new data model (that serves as the foundation of a report), needs a set of data. This set of data is returned by a “query” that the user can create himself or that he can reuse from a shared pool of queries (created by other users).

Basically, Power Query is a Microsoft Excel 2013 add-in that lets almost anyone create queries from data coming from a wide variety of data sources and data types. Power Query also allows users to manage these queries and to share them across the company.

Power Query can be seen as a self-service ETL tool for cleaning up and transforming data from different kinds of data sources, a role that SQL Server Integration Service has been playing very well for years in the traditional Microsoft BI stack.

Keep in mind that what we are going to illustrate in this article are just two sequences of the BI process we illustrated in our previous article.

Installing Power Query

Download the add-in from the following Microsoft download web site and once installed, activate it in the File-Option menu (see Figure 2).

Figure 2: Power Query Excel Add-in

When activated, it will show-up in the ribbon (see Figure 3).

Figure 3: Activated Ribbon

Creating Queries

Queries can be created from many types of data sources (see Figure 4), which is very convenient.

Figure 4: Power Query Data Sources

Queries can be created from the following sources:

External Data :

Excel Data:  A table within the current Excel workbook.

Let’s now go through the whole process by showing how to create queries, and how to share and retrieve them.

Creating Queries

Let’s take a very simple example: We have created a list of food items in a SharePoint List and we want to query this list.

Connect Excel 2013 to this SharePoint Online list by providing the SharePoint site url (see Figure 5).

Figure 5: Connecting Power Query to a SharePoint List

Power Query fetches and displays the SharePoint lists of the web site (see Figure 6).

Figure 6: Power Query Displays Lists of SharePoint Sites

Let’s select the list (here “PicNicItems”) and create a query by displaying the Power Query Editor (Figure 7).

Figure 7: Creating a Query Based on a SharePoint List

Excel will then load the selected data (the SharePoint List data) in the Power Query editor (see Figure 8). We still have to transform the data (or to reorganize them) in order to focus only on what we need.

Figure 8: Creating a Query with the Power Query Editor

The Power Query editor is a really amazing tool for creating queries, but it also allows many kinds of data manipulations; all actions in this editors will be (automatically) scripted in a language called “M” and will also be recorded in such way they can be repeated when the query will be triggered by the user or by a data model.

In our specific case for instance, we will rename the column “Name1” as “Name”, rename “Type1” as “Type”, and switch “ItemID” and “Type” as illustrated in Figure 9.

Figure 9: Renaming Columns in the Power Query Editor

Let’s remove the unnecessary columns (see Figure 10).

Figure 10: Removing Columns from a Query in the Power Query Editor

As mentioned before, all activities in the editor are recorded (see “Applied Steps” in Figure 11), we can always remove some recorded steps and get back to a previous one.

Figure 11: Power Query Activities

An advanced editor (see Figure 12) allows code editing if needed, even if the good practice is to let Excel generate the “M” code.

Figure 12: Controlling Generated code with the Power Query Advanced Editor

The query can be documented and loaded in an Excel worksheet (“Load to worksheet”) or stored in the Excel 2013 data Model (“Load to Data Model”) (see Figure 13).

Figure 13: Describing the Query

The queries associated with the workbook can be visualized in the Workbook Queries panel.

Figure 14: Queries Stored in the Workbook

As illustrated in Figure 14, the query at this stage is still locally stored in the workbook. But how can we share a query?

Sharing Queries

Once the query is created, we have the possibility to share our work with other users by saving it in Office 365 (see Figure 15). However this option requires a Power BI software License in order to have the business intelligence infrastructure in place in Office 365.

Figure 15: Sharing a Query

Additional details can be provided, like who can use the query (“Share with”) (see Figure 16), or even a link to a document describing the query.

Figure 16: Sharing a Query, More Options

The option “Certify this query for others”, provides an additional indication that the query has been tested. This option is only available to members of a specific BI group: the “data steward” group. Again, this option is available if a Power BI license has been purchased. In the Power BI admin center (see Figure 17), you can add users to the data steward group (see Figure 18).

Figure 17: Power BI Admin Center

Figure 18: Adding Users to the Data Steward Group

Finding Shared Queries

Finding an existing query can easily be done by using Power Query. The user must first log in in the organization (with an Office 365 account) (see Figure 19).

Figure 19: Power BI Login from Excel 2013

The user can access the “Online Search” button (see Figure 20) in the Power Query ribbon.

Figure 20: Searching for a Shared Query

The Online search panel will show up (see Figure 21). There, the user can type any keyword that matches the query name or its description.

The search result can be narrowed down to queries shared in the data catalog (within the organization, see “2” in Figure 21) or to queries shared by (US) governmental or public bodies (see “PUBLIC” in Figure 21). The user can easily choose the query needed by previewing the data returned by the query (see “3” in Figure 21).

Figure 21: Searching for and Using a Shared Query

A certified icon appears alongside the query name to let users easily distinguish a certified query (see “4” in Figure 21).

The query can then be selected, loaded to Excel, or added to the data model (by going to the PowerPivot ribbon as Figure 22 illustrates).

Figure 22: Adding a Query to the Data Model

Managing Shared Queries

Managing queries means updating, listing, and sharing the existing shared queries.

The “Shared” button (Figure 23)  is the usual way to list shared queries that can seamlessly be grabbed from the Office 365 data catalog. As we mentioned before, any sharing activity requires a sign-in and therefore a  Power BI software license.

Figure 23: Managing Shared Queries

Query contributors can also manage their own queries by leveraging the Power BI App functionalities. This app can be added to any SharePoint site (and works as long as you have at least one Power BI license).

To access the queries the user shared, the user can just right-click a query and select View Statistics as Figure 24 illustrates, to be shown Query Analytics (Figure 25).

Figure 24: Managing Shared Queries: How to View Query Statistics

Figure 25: Managing Queries: Statistics in the Power BI App

Clicking my queries (on the left rail, see Figure 26) will display the list of queries the current user has shared in the data catalog as well as the associated Data Source Status.

Figure 26: Shared Queries in the Data Catalog

Several queries can potentially be associated to the same data source. In the case of the “Contoso Food” query that we created in the beginning of this article, the data source is a SharePoint list.

Queries in Depth

Let’s get a little bit deeper in Power Query by looking at the M language. See Part 3.