Data analysis

What is Power Query?
Power Query is a data transformation and data preparation engine. Power

Query comes with a graphical interface for getting data from sources and a
Power Query Editor for applying transformations. Because the engine is
available in many products and services, the destination where the data will
be stored depends on where Power Query was used. Using Power Query, you
can perform the extract, transform, and load (ETL) processing of data.
Diagram with symbolized data sources on the left, passing through Power
Query for transformation in the center, and then going to four destinations
on the right: Microsoft Azure Data Lake Storage, Microsoft Dataverse,
Microsoft Excel and Microsoft Power BI.
How Power Query helps with data acquisition

Business users spend up to 80 percent of their time on data preparation,
which delays the work of analysis and decision-making. Several challenges
contribute to this situation, and Power Query helps address many of them.
Expand table
Existing challenge How does Power Query help?
Finding and connecting to data Power Query enables connectivity to a wide range of data
is too difficult sources, including data of all sizes and shapes.
Experiences for data Consistency of experience, and parity of query capabilities

connectivity are too fragmented over all data sources.
Data often needs to be Highly interactive and intuitive experience for rapidly and
reshaped before consumption iteratively building queries over any data source, of any size.
Any shaping is one-off and not When using Power Query to access and transform data, you
repeatable define a repeatable process (query) that can be easily
refreshed in the future to get up-to-date data.
In the event that you need to modify the process or query to
account for underlying data or schema changes, you can use
the same interactive and intuitive experience you used when
you initially defined the query.
Volume (data sizes), velocity Power Query offers the ability to work against a subset of the
(rate of change), and variety entire dataset to define the required data transformations,
(breadth of data sources and allowing you to easily filter down and transform your data to
data shapes) a manageable size.
Power Query queries can be refreshed manually or by taking
advantage of scheduled refresh capabilities in specific
products (such as Power BI) or even programmatically (by
using the Excel object model).
Because Power Query provides connectivity to hundreds of
data sources and over 350 different types of data
transformations for each of these sources, you can work with
data from any source and in any shape.
Power Query experiences

The Power Query user experience is provided through the Power Query
Editor user interface. The goal of this interface is to help you apply the
transformations you need simply by interacting with a user-friendly set of
ribbons, menus, buttons, and other interactive components.
The Power Query Editor is the primary data preparation experience, where
you can connect to a wide range of data sources and apply hundreds of
different data transformations by previewing data and selecting
transformations from the UI. These data transformation capabilities are
common across all data sources, whatever the underlying data source
limitations.
When you create a new transformation step by interacting with the

components of the Power Query interface, Power Query automatically
creates the M code required to do the transformation so you don't need to
write any code.
Currently, two Power Query experiences are available:
 Power Query Online—Found in integrations such as Power BI

dataflows, Microsoft Power Platform dataflows, Azure Data Factory
wrangling dataflows, and many more that provide the experience
through an online webpage.
 Power Query for Desktop—Found in integrations such as Power Query
for Excel and Power BI Desktop.
Note
Although two Power Query experiences exist, they both provide almost the
same user experience in every scenario.
Transformations
The transformation engine in Power Query includes many prebuilt
transformation functions that can be used through the graphical interface of
the Power Query Editor. These transformations can be as simple as removing
a column or filtering rows, or as common as using the first row as a table
header. There are also advanced transformation options such as merge,
append, group by, pivot, and unpivot.
All these transformations are made possible by choosing the transformation

option in the menu, and then applying the options required for that
transformation. The following illustration shows a few of the transformations
available in Power Query Editor.
Why Do I Need to Unpivot My Data?

Often times we receive data that is NOT in the right format for use in a pivot
table.
This could be a report that is exported from a system of record, or it could be

a spreadsheet that is being manually updated. Either way, getting this data
in the correct layout is the most important step in creating a pivot table.
I have other articles that explain why the structure of the source data is so
important and how pivot tables work. Both these articles should help you
understand why this process is so critical.
Power Query’s Unpivot Button

Fortunately, Power Query has an Unpivot Button that makes it really fast and
easy to unpivot in Excel.
It’s basically a 3-step process:
1. Load your data into Power Query (create a query)
2. Unpivot the selected columns to normalize the data.

3. Output the results to a new worksheet in Excel.
The Unpivot Columns button is located on the Transform tab of the Power
Query Editor window.
Checkout my article on an Overview of Power Query if you haven't heard of

this awesome (free) tool.
A Simple Example of Unpivot

The following screencast shows how to unpivot a sheet that contains
inventory data.
The original sheet is laid out in a horizontally with columns for the different
products. To use this data as the source of a pivot table, we need to put all
the product names in a single column and all the inventory values in another
column.
The Unpivoted Table in the image above can be used as the source data of a
pivot table. This layout will make it easy to create summarized reports with
different layouts.

Data analysis

Uploaded by

Copyright:

Available Formats

Data analysis

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Data analysis

Uploaded by

Copyright:

Available Formats

What is Power Query?

Power Query is a data transformation and data preparation engine. Power

How Power Query helps with data acquisition

Existing challenge How does Power Query help?

Experiences for data Consistency of experience, and parity of query capabilities

Power Query experiences

When you create a new transformation step by interacting with the

Currently, two Power Query experiences are available:

 Power Query Online—Found in integrations such as Power BI

All these transformations are made possible by choosing the transformation

Why Do I Need to Unpivot My Data?

This could be a report that is exported from a system of record, or it could be

Power Query’s Unpivot Button

It’s basically a 3-step process:

1. Load your data into Power Query (create a query)

2. Unpivot the selected columns to normalize the data.

Checkout my article on an Overview of Power Query if you haven't heard of

A Simple Example of Unpivot

You might also like