POWER QUERY

WHAT IS POWER QUERY?

Power Query is a data connection technology that allows you to discover, connect,
combine and refine data sources to meet your analysis needs. The resources
Power Query is available in Excel and Power BI Desktop.

To use Power Query, it is necessary to follow some steps.

Although some attempts at data analysis focus on some of these steps,

Each of these steps is an important element of Power Query.

COMO FAÇO PARA OBTER O POWER

QUERY?
Power Query is availableas a download for Excel 2013 and 2010. The technology of
underlying query that allows Power Query is also found in the desktop of
Power BI, which is part of Microsoft’s overall Power BI offering.

Select the button below to download the current version of Power Query:

You can also see the news of Power Query.

Note: Power Query is known as the Get & Transform feature in Excel 2016 and
2019.
 POWER QUERY
INTRODUCTION TO POWER QUERY
With Power Query, you can search for data sources, make connections, and in
next, format your data (for example, remove a column, change a data type or
merge tables) in ways that meet your needs. After shaping your
Data, you can share your findings or use your query to create reports.

By observing these steps in order, they usually occur this way:

• Connect: establish connections with data located in the cloud, in a service, or locally

• Transform: model the data to meet your needs; the original source
remains unchanged

• Combine: create a data model from multiple data sources and obtain a display
exclusive to them

• Share: as soon as your appointment is completed, you can save it, share it or
use it for reports

Power Query records are used at each step, and allow you to modify these.
stages in any way you need. It also allows undoing, redoing, and changing the order
or modify any step... all of this so that you can see your connected data
exactly as desired.

With Power Query, you can create queries that are as simple or as complex as
wish. And since Power Query uses M Language to log and execute its steps, you
 POWER QUERY
you can create queries from scratch (or adjust them manually) to leverage the power and the
flexibility of the data script, everything in Power Query.

Connect
You can use Power Query to connect to a single data source, such as a folder.
of Excel work, or it can connect to various databases, feeds, or services
scattered in the cloud. With Power Query, you can gather all these sources using your
your own unique combinations and discover information that you otherwise would not have seen.

Connect to data sources in the Power Query ribbon, in the Get Data section
External or in the Data tab. Data sources include data from the web, from files, from
databases, from Azure, from other sources or even tables in a folder of
Excel work.

When you connect to a data source, a Visualization pane will appear. Click
to take charge, you want to work with the data in Excel immediately. But if you want
to apply transformations or format the data in advance, click on Edit. The Power
Query will start the Query Editor: a dedicated window that facilitates and displays your connections.
 POWER QUERY
of data and transformations that you apply. The next section, Transform, provides more
information about the Query Editor.

Transform
Power Query allows you to transform the data from your connections in a way that helps
you to analyze them. Transforming data means modifying them in some way to meet the
your needs - for example, you can remove a column, change a data type or
merging tables - each of them is a data transformation. As the data are
Transformed, collectively, they take the shape you need to advance your analysis.
The process of applying transformations to one or more data sets is often
called finger pointing the data.

Power Query uses a dedicated window called the Query Editor to facilitate and
display data transformations. You can open the Query Editor by selecting Start
In the Power Query ribbon or on the Data tab.
 POWER QUERY

The Query Editor is also opened whenever you connect to a data source.
create a new query or load an existing query.

Power Query tracks everything you do with the data. The Query Editor logs and
label each transformation or step that you apply to the data. Regardless of whether it
 POWER QUERY
transformation to be a data connection (a data source), removal of a column,
merging or changing data types, the Query Editor tracks each operation in
STAGES APPLIED of the Consultation Settings panel.

The transformations you apply to your data connections collectively constitute

your consultation

It is important (and useful) to understand that Power Query does not alter the original source data.
instead, Power Query records each step that is performed in the connection or transformation of
data and, as soon as you finish shaping the data, it takes a snapshot of the data
refined defined and takes it to Excel.

There are several transformations you can apply to the data. You can also create
your own transformations using the M Language (which is how Power Query records the
stages in the background), using the Advanced Editor of the Query Editor. It is possible to open
the Advanced Editor in the ribbon transforms the Query Editor, where it is possible
modify the steps of the Language M associated with the existing query. You can also create
queries from scratch using the Advanced Editor.
 POWER QUERY

Share
When you save an Excel workbook that contains a query, it is also
automatically save. You can display all queries in an Excel workbook
selecting Show Panel in the Section Folder Work Queries in the ribbon
in Power Query or on the Data tab.
 POWER QUERY

The Workbook Queries panel shows all the queries in the workbook.

But why stop there? With Power Query and the Data Catalog, you
You can share your queries with anyone in your organization. Or create a
query that you will use frequently, use it in several workbooks and save the work
yourself. Instead of saving and sending Excel workbooks via email (and trying to guess
which version is the original, what has changed or if your data is outdated!), save a query
in the Data Catalog and avoid the hassle of countless versions of the workbook not
tracked filling the inboxes. Just right-click on one
consultation on the Work Folder panel and a menu provides all types of
option, including Send to Data Catalog.
 POWER QUERY

Also, observe the other options in the right-click menu. You can duplicate one.
query, which allows for the alteration of certain elements (or all elements) of a query
without changing the original query; it's like creating a query template that you can modify
to create custom datasets, such as a retail dataset, another
for wholesale and another for inventory, all based on the same data connections.

It is also possible to merge or add queries, which allows for transforming queries into
reusable building blocks.
 POWER QUERY
With Power Query, you can be creative with your data, your connections and your
transformations, in addition to expanding your work by sharing it with other people (or with yourself
even when it is on another device).

With the Data Catalog, you can also easily see all your queries.
shared.

The Queries panel of My Data Catalog is open, showing all the queries that
you shared. From there, you can load a query, edit it, or in some way,
use it in the workbook you are currently working on.
 POWER QUERY

With your query completed, you can use it to create reports in Excel, Power
ViewouPower BI.
 POWER QUERY
LINKS ÚTEIS
Below, a series of interesting links to delve into Power Query:

▪ Import data from external data sources (Power Query)

▪ Connect to an Excel data table (Power Query)

▪ Connect to a SQL Server database (Power Query)

▪ Combine data from multiple data sources (Power Query)

▪ Introduction to the Query Editor (Power Query)

▪ Add a query to an Excel spreadsheet (Power Query)

▪ Edit query step settings (Power Query)

▪ Shaping data (Power Query)

▪ Display and manage queries in a workbook (Power Query)

▪ Combine multiple queries (Power Query)

▪ Merge Queries (Power Query)

▪ Create a Data Model in Excel

▪ Modify a formula (Power Query)

▪ Learn more about Power Query formulas

▪ Create an advanced query (Power Query)

▪ Power Query M formula Language

▪ Microsoft Power Query help for Excel

▪ The Power Query forum

▪ Power Query download

▪ Power BI Blog (includes Power Query posts)

▪ Combine data from multiple data sources (Power Query)

 POWER QUERY
REFERENCES
The content of this material has been adapted from Microsoft documentation (Introduction to
Power Query), available at:

[Link]
9e62-4cb9-a02e-5bfb1a6c536a