An Introduction to the Power Query Editor

Published on Apr 09,2020 737 Views
Upasana
Research Analyst, Tech Enthusiast, Currently working on Azure IoT & Data Science... Research Analyst, Tech Enthusiast, Currently working on Azure IoT & Data Science with previous experience in Data Analytics & Business Intelligence.

With Power BI   you can connect to the world of data, create compelling and interactive reports, share your efforts with others, and expand their business intelligence efforts. The Power Query  helps you to connect to sources, shape and transform the data to meet your needs.

This article provides an overview of the feature, but there’s way more to learn and the more you play around with your data and understand the feature, the more powerful a tool you’ve got. Now, it’s not everything you need to know but the following topics are a great way to start out:

So, let’s get acquainted with this piece of technology.


Power Query Overview

So what exactly is Power Query?

What is Power Query?

Power Query is Microsoft’s Data Connectivity and Data Preparation technology. It basically, enables business users to access data stored in data sources seamlessly whilst, reshaping it to fit their needs. It’s easy to use, engaging, even convenient to use for the no-code users.

Note: A lot of you might get confused in M and DAX. Let me clarify once and for all that both of them are vastly different from one another. While M is a mashup query language used to query a multitude of data sources, DAX (or Data Analysis eXpressions) is a formula language which is used to work on data that is stored in tables.

Data Sources

Supported data sources include a wide range of file types, databases, Microsoft Azure services, and many other third-party online services. It also provides a Custom Connectors SDK so that third parties can create their own data connectors and seamlessly plug them into Power Query.

Data Sources - Power Query - Edureka

Power Query Editor

The Power Query Editor is the primary data preparation experience natively integrated into several Microsoft products, including but not limited to Microsoft Excel, Microsoft Power BI, Microsoft SQL Server Data Tools, etc. This, in turn, allows users to apply over 300 different data transformations by previewing data and selecting transformations in the user experience.

power query editor - power query - edureka

These data transformation capabilities are common across all data sources, despite the underlying data source limitations.

Power Query in Power BI

Power BI Desktop comes equipped with Power Query Editor. You can use the Power Query Editor to connect to one or many data sources, shape and transform the data. You could modify the data in hand to meet your needs, make it more usable, and then load that model into Power BI Desktop.

To get to the Query Editor, select Edit Queries from the Home tab of Power BI Desktop. With no data connections, the Query Editor appears quite dull, a blank pane as it should.

edit queries - power query - edureka
But once a query is loaded, this Editor view becomes way more interesting. If we connect to a data source, the Query Editor loads information about the data, which you can then begin to shape before basing your model on it.

M: Power Query Formula Language

Microsoft Power Query provides a powerful data import experience that encompasses many features. Power Query works with Analysis Services, Excel, and Power BI workbooks.

A core capability of Power Query is to filter and combine data from a rich collection of data sources that it supports. Any such data mashup is expressed using a functional, case sensitive language known as M Formula Language.

It is pretty similar to F# and is used to query a multitude of data sources. It contains commands to transform data and can return the results of the query to either an Excel table or Power BI data model.

Quickstart: How to use Power Query in Power BI?

Let’s get acquainted with the Query Editor.

So, once you’ve gotten to the query editor, you’ll have no data connections. The Query Editor appears as a blank pane, ready for data.

query editor no data - edureka

If you connect to this web data sourcethe Query Editor loads information about the data. You can then begin to shape and transform the same.

Once a data connection is established, the Query Editor appears to be something like the following:

  1. The ribbon has many buttons, which are now active to interact with the data in the query.
  2. In the left pane, queries are listed and available for selection, viewing, and shaping.
  3. In the centre pane, data from the selected query is displayed and available for shaping.
  4. The Query Settings window appears, listing the query’s properties and applied steps.

Query Editor Data Sources - Edureka

Power Query Advanced Editor

If you want to see the code that the Query Editor is creating with each step or want to create your own shaping code, you can use the Advanced Editor.

  • To launch the advanced editor, select View from the ribbon, then select Advanced Editor option. A window appears, showing the existing query code.

Advanced Editor - Edureka

  • 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 the Editor apply the changes to the data model.

  • To do so, select Close & Apply from Power Query Editor’s File menu.

close and apply - Edureka

As progress is made, Power BI Desktop provides a dialogue to display its status.

Once you have your query, make sure your work is saved, Power BI Desktop can save your work in a .pbix file.

  • To save your work, select File > Save As or if you’re not saving it for the first time you could File > Save.

Summary

In this article, you learned about the Power Query feature, its various databases and M language. You also got a brief idea on how to use Query Editor and Advanced Editor in Power BI Desktop whilst learning how to connect to data sources and save your query.

To learn more concepts on Power BI, then check out our Power BI Training Certification which comes with instructor-led live training and real-life project experience. This training will help you understand Power BI in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section and we will get back to you.

Comments
0 Comments

Join the discussion

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.