-->

Download Power Query

With Power Query in Power BI, you can connect to many different data sources, transform the data into the shape you want, and quickly be ready to create reports and insights. When using Power BI Desktop, Power Query functionality is provided in Power Query Editor.

1. Power Query is Microsoft’s self-service data preparation technology which provides an intuitive and highly visual experience for non-technical users to easily connect to hundreds of data sources, reshape and clean data from these sources in a homogeneous and consistent way, and seamlessly combine data from one or multiple data sources together so that it can be used in downstream scenarios such as data reporting, analytics, low-code application development, and more.
2. As Power Query records the transformation steps, it includes many hardcoded values within the M code. For example, if we filter a column to select all values greater than 50, the 50 will be a hardcoded value in the M code. Or if we import a CSV file, the file path is hardcoded into the query. What if we want to change these values or file paths?
3. Power Query is the easiest way to connect, extract, transform and load data from a wide range of sources.

In the POWER QUERYribbon tab, choose From Other Sources Blank Query. In the Query Editor formula bar, type = Text.Proper('text value'), and press Enteror choose the Enter icon. Power Query shows you the results in the formula results pane. To see the result in an Excel worksheet, choose Close & Load.

Let's get acquainted with Power Query Editor.

Microsoft Power Query For Excel Download

If you're not signed up for Power BI, you can sign up for a free trial before you begin. Also, you can download Power BI Desktop for free.

Using Power Query Editor

Power Query is available in Power BI Desktop through Power Query Editor. To open Power Query Editor, select Edit Queries from the Power BI Desktop Home tab.

With no data connections, Power Query Editor appears as a blank pane, ready for data.

As soon as a query is loaded, the Power Query Editor view becomes more interesting. If you connect to the following Web data source, Power Query Editor loads information about the data, which you can then begin to shape.

The following image shows how Power Query Editor appears after a data connection is established.

| No. | Description ||---------|-------------|| 1 | On the ribbon, many buttons are now active so you can interact with the data in the query. || 2 | In the Queries pane, queries are listed and available for selection, viewing, and shaping. || 3 | In the center pane, data from the selected query is displayed and available for shaping. || 4 | In the Query Settings pane, the properties and applied steps for the selected query are listed. |

The following sections describe each of these four areas—the ribbon, the Queries pane, the data view, and the Query Settings pane.

The query ribbon

The ribbon in Power Query Editor consists of five tabs—Home, Transform, Add Column, View, and Help.

The Home tab contains the common query tasks, including the first step in any query, which is Get Data. The following image shows the Home ribbon.

To connect to data and begin the query building process, select the Get Data button. A menu appears, providing the most common data sources.

You use the Transform tab to access common data transformation tasks, such as adding or removing columns, changing data types, splitting columns, and others important to know that the underlying data is not changed; rather, Power Query Editor adjusts and shapes its view of the data, and any interaction it has with the underlying data is based on that shaped and modified view.

In the Query Settings pane, you can rename steps, delete steps, or reorder the steps as you see fit. To do so, right-click to select the step in the Applied Steps section, and choose the action you want. All query steps are carried out in the order in which they appear in the Applied Steps pane.

The advanced editor

If you want to see the code that Power Query Editor is creating with each step, or want to create your own shaping code, you can use the advanced editor. To open the advanced editor, select the View tab on the ribbon, and then select Advanced Editor. A window appears, showing the existing query code.

You can directly edit the code in the Advanced Editor window. To close the window, select the Done or Cancel button.

Saving your work

When your query is where you want it, you can have Power Query Editor apply the changes to the data model in Power BI Desktop and then close Power Query Editor. To do that, on the Power Query Editor File menu, select Close & Apply.

As it applies the changes in your query, Power BI Desktop displays the status of the operation.

After you have your query where you want it, or if you just want to make sure your work is saved, Power BI Desktop can save your work in a .pbix file.

To save your work as a .pbix file in Power BI Desktop, select File > Save (or File > Save As), as shown in the following image.

Next step

In this quickstart, you learned how to use Power Query Editor in Power BI Desktop and how to connect to data sources. To learn more, continue with the tutorial on shaping and transforming data with Power Query. On screen video recorder for mac.

Summary

Connecting to Microsoft Graph REST APIs from Power Query isn't recommended or supported. Instead, we recommend users explore alternative solutions for retrieving analytics data based on Graph, such as Microsoft Graph data connect.

Users may find they can make certain REST calls to Microsoft Graph API endpoints work through the Web.Contents or OData.Feed functions, but these approaches are not reliable as long-term solutions.

This article outlines the issues associated with Microsoft Graph connectivity from Power Query and explain why it isn't recommended.

Authentication

The built-in Organizational Account authentication flow for Power Query’s Web.Contents and OData.Feed functions isn't compatible with most Graph endpoints. Specifically, Power Query’s Azure Active Directory (Azure AD) client requests the user_impersonation scope, which isn't compatible with Graph’s security model. Graph uses a rich set of permissions that aren't available through our generic Web and OData connectors.

Ms Power Query If Statement

Implementing your own Azure AD credential retrieval flows directly from your query, or using hardcoded or embedded credentials, also isn't recommended for security reasons.

OData Libraries’ Incompatibility

Ms Power Query For Excel 2016

Certain Graph endpoints and extensions to Graph may require the use of OData libraries and features that aren't supported by Power Query’s built-in OData.Feed function because Graph and Power Query may be using two different versions of OData libraries. These issues generally result in errors retrieving the service’s $metadata document. Users may discover common guidance related to passing the Implementation = '2.0' option to the OData.Feed function call to ensure the latest supported OData libraries are used. While this approach does resolve certain OData incompatibilities, users may still encounter errors over time as Graph and Power Query adopt new versions of the OData libraries at different times.

Performance

The Microsoft Graph API is designed to support many application scenarios but is suboptimal for the large-scale data retrieval required for most analytics scenarios. Users trying to retrieve large amounts of data from Graph APIs may encounter performance issues. Details around scenario applicability can be found in the Graph documentation.

Using a Custom Connector

Excel Power Query Tutorial

Some Power Query users have enabled Graph connectivity through custom connectors, limiting their functionality to certain parts of the Graph API. This approach allows connector developers to resolve general authentication issues by defining their own Azure AD client with Graph specific permissions. Some custom connectors work around OData challenges by using Web.Contents and simulating OData support within their connector logic. However, this approach isn't recommended as users frequently hit the performance and scalability issues described above. Developers who take this route should continue with these limitations in mind.