Ms Power Query

Posted on by



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. In this step-by-step tutorial, learn how you can use Microsoft Power Query to get and clean up data in both Microsoft Excel and Microsoft Power BI.

-->

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.

Let's get acquainted with Power Query Editor.

  • 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.
  • With Power Query you can define multiple tables in your data model with relationships across them, or use the Merge/Append capabilities to combine them into a single table.
  • 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.

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.

Power

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.

Ms Power Query Editor

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.

We have recently shipped, in public preview, Power Query Online integration for Microsoft Flow. Using this capability allows flow makers to transform and shape their data coming from SQL Server using Power Query Online.

Why did we build this capability?

We built this capability for many reasons including:

  • An alternative to OData which can be cumbersome to use for many of our users.
  • A simpler approach to joining tables than T-SQL or writing Stored Procedures.
  • Future opportunities to include additional data sources in mashups that allow makers to build data transformations across multiple data sources.

Licensing

The Power Query functionality will be included in our Premium Connector offering, which requires a Flow Plan 1 or higher. Currently this is not enforced as part of the preview, but will be in the future.

Scenario

Let’s take a closer look at how we can use this new capability to enhance our data extraction capabilities.

In this scenario, we have an Apartment Rental company called Contoso Apartments. Naturally, customers will inform the main office when there are maintenance issues across their many properties. Much like any Work Order system, data is organized in multiple tables including Customers, Apartments and Work Orders. The Customer Service representatives are very interested in keeping their customers happy and want to proactively ensure that customers are content. If they are not, they want to quickly course-correct to avoid them leaving to live somewhere else.

We can monitor customer sentiment by ‘mashing’ up our Customer, Apartment and Work Order data from Power Query. When we do this, we have aggregated data that can be passed to a sentiment analysis tool. When we detect that a customer is unhappy, we can then publish a message to Microsoft Teams where a customer service rep can follow-up with the customer.

To build this solution we will perform the following:

  • We will add a Recurrence trigger that will run every day
  • Next, we will locate our Transform data using Power Query action which is provided as part of the SQL Server connector.

Ms Power Query Add In

  • To connect to SQL Server, we need to create a Connection that includes a SQL Server name, Database name, Username and Password.
  • With our connection established, we can now create a Power Query query.
  • Our next step is we need to select the tables that we would like to include in our mash-up. In this case we are going to select Customers, Apartments and Work Orders.
  • We want to join these different tables so that we have enriched Work Order data that includes Customer and Apartment related data for our Work Orders. To do this, we will click on Combine tables and then select Merge queries as new.
  • When we merge, we need to select the type of join that we want to use. In this case we will select a Left Join and declare our Work Order table as our core table that we want to enrich with Customer and Apartment data.

Note: In this scenario, I ran the Merge twice. Once with Work Orders and Customers and then once again with my (WorkOrders and Customers) + Apartment.

  • Once we have merged our tables, we can now trim our dataset by only including the columns that we need.
  • Before we configure the rest of our Flow, we do need to declare our new aggregated query as the query we will Enable Load for.

Note: At this time, you can only enable 1 query that will return back to Flow. However, as we have discovered we can merge multiple queries into a single query for our use.

  • With our Power Query query configured, we can now use the result set and dynamic content, much like we can do with other connectors. In our use case, what we will do with our result set is loop through each record returned and send the Work Order Comments from the customer through the Azure Cognitive Services sentiment analysis API.
  • Next, we will evaluate the sentiment returned to see if it is less than .4 (which is really bad). When this occurs, we will add related Apartment, Customer and Work Order information with this sentiment value and add to an Array. After we have iterated through all of these recent Work Orders, we will then check the length of the array to see if we have records. If we do have records, we will convert this Array to an HTML table which we can then publish to a Microsoft Teams Channel.

Testing

We can now go and run this flow from within the Microsoft Flow maker portal or by calling it from the Microsoft Teams Flow Bot. Once the flow runs, it will publish the results of our flow in Microsoft Teams. This allows the Customer Service channel to target customers who are unhappy without performing a lot of data exploration.

Features

  • Only SQL Server is supported as a data source. This is deliberate in our first release as we do not want to expose additional data sources that are not protected by Microsoft Flow Data Loss Prevention (DLP) policies. We do want to include additional data sources, but those will be future investments.
  • We do throttle Power Query Online usage based upon:
    • 2 Hours/Day
    • 10 Hours/Week

Ms Power Query

This is based upon the amount of time it takes for your Power Query queries to execute. If these values don’t work for you, we would love to hear what they need to be.

Ms Power Query Excel 2016

  • As described previously, we will only output 1 query. To avoid unexpected results, ensure that you Enable Load on the desired query.

Ms Power Query Tutorial

What’s Next?

Ms Power Query For Excel

Working with the Power Query team to unlock this capability has been really exciting. Both teams see an opportunity to empower Power platform users to do more using these technologies. Since Power Query is a very rich and deep platform, we would love to hear more about Power Query + Flow use cases that you envision. This feedback will help us prioritize future investments. Please comment below.