Power Query is an
ETL tool created by
Microsoft
Microsoft Corporation is an American multinational technology corporation producing computer software, consumer electronics, personal computers, and related services headquartered at the Microsoft Redmond campus located in Redmond, Washing ...
for
data extraction
Data extraction is the act or process of retrieving data out of (usually unstructured or poorly structured) data sources for further data processing or data storage (data migration). The import into the intermediate extracting system is thus usual ...
, loading and
transformation
Transformation may refer to:
Science and mathematics
In biology and medicine
* Metamorphosis, the biological process of changing physical form after birth or hatching
* Malignant transformation, the process of cells becoming cancerous
* Trans ...
, and is used to retrieve data from sources, process it, and load them into one or more target systems. Power Query is available in several variations within the
Microsoft Power Platform
Microsoft Power Platform is a line of business intelligence, app development, and app connectivity software applications. Microsoft developed the Power Fx low-code programming language for expressing logic across the Power Platform. It also p ...
, and is used for
business intelligence
Business intelligence (BI) comprises the strategies and technologies used by enterprises for the data analysis and management of business information. Common functions of business intelligence technologies include reporting, online analytical pr ...
on fully or partially self-service platforms. It is found in software such as
Excel
ExCeL London (an abbreviation for Exhibition Centre London) is an exhibition centre, international convention centre and former hospital in the Custom House area of Newham, East London. It is situated on a site on the northern quay of the ...
,
Power BI
Power BI is an interactive data visualization software product developed by Microsoft with a primary focus on business intelligence. It is part of the Microsoft Power Platform.
Power BI is a collection of software services, apps, and connectors t ...
,
Analysis Services
Analysis ( : analyses) is the process of breaking a complex topic or substance into smaller parts in order to gain a better understanding of it. The technique has been applied in the study of mathematics and logic since before Aristotle (38 ...
,
Dataverse
The Dataverse is an open source web application to share, preserve, cite, explore and analyze research data. Researchers, data authors, publishers, data distributors, and affiliated institutions all receive appropriate credit via a data citation w ...
,
Power Apps
Microsoft Power Platform is a line of business intelligence, app development, and app connectivity software applications. Microsoft developed the Power Fx low-code programming language for expressing logic across the Power Platform. It also pro ...
,
Azure Data Factory,
SSIS,
Dynamics 365
Microsoft Dynamics 365 is a product line of enterprise resource planning (ERP) and customer relationship management (CRM) intelligent business applications announced by Microsoft in July 2016 and released on November 1, 2016.
Feature applicat ...
, and in cloud services such as
Microsoft Dataflows, including Power BI Dataflow used with the online Power BI Service or the somehwat more generic version of Microsoft Dataflow used with
Power Automate
Microsoft Power Automate, formerly known as Microsoft Flow until November 2019, is an iPaaS platform by Microsoft for automation of recurring tasks. It is part of the Microsoft Power Platform line of products together with products such as Power A ...
.
ETL is closely related to
data modeling
Data modeling in software engineering is the process of creating a data model for an information system by applying certain formal techniques.
Overview
Data modeling is a process used to define and analyze data requirements needed to suppo ...
,
and for transformation, Power Query can be used to develop a logical data model in those cases where the data does not already have one, or where there is a need to further develop the
data model
A data model is an abstract model that organizes elements of data and standardizes how they relate to one another and to the properties of real-world entities. For instance, a data model may specify that the data element representing a car be co ...
.
History
Power Query was included as an additional feature in
Power Pivot
Power Pivot, formerly known as PowerPivot (without spacing), is a feature of Microsoft Excel, a computer software spreadsheet. It is available as an add-in in Excel 2010, 2013 in separate downloads, and as an add-in included with the Excel 2016 ...
(used for making
pivot tables, and more) in Excel 2010 and 2013. In Excel 2016, the function was renamed Get & Transform for a short time, but has since been named Power Query again.
M Formula language
Power Query is built on what was then{{when, date=December 2022 a new
query language
Query languages, data query languages or database query languages (DQL) are computer languages used to make queries in databases and information systems. A well known example is the Structured Query Language (SQL).
Types
Broadly, query language ...
language called M. It is a
mashup language (hence the letter M) designed to create queries that mix together data. It is similar to the
F Sharp programming language, and according to Microsoft it is a "mostly pure, higher-order, dynamically typed, partially lazy, functional language." The M language is case-sensitive.
Much of the user interaction with Power Query can be done via
graphical user interfaces
The GUI ( "UI" by itself is still usually pronounced . or ), graphical user interface, is a form of user interface that allows users to interact with electronic devices through graphical icons and audio indicator such as primary notation, inste ...
with
wizards, and this can be used for many common or basic tasks. It is also possible to use the advanced editing mode where the developer can write in the M formula language; this gives greater expressive power, more possibilities, and can also be used to change the code generated by the graphical wizards.
Let function
A Power Query written in M consists of a ''let'' expression where data and expressions can be referenced between each other, and an ''in'' expression which contains the output. Lines in the let expression (which are also called steps) are separated by a comma at the end of each line, except for the last line. Comments are written in C-style, with inline comments beginning with a forward slash (
/
) symbol, while block comments which can span multiple lines are delimited by
/*
and
*/
. The M language is powerful and is sometimes necessary for performing certain forms of queries, but for many common tasks, however, users can also often interact with Power Query through the graphical user interfaces without directly working with the M language.
DirectQuery
In Power BI, use of M-code is somewhat limited in DirectQuery, as opposed to Import which has all capabilities. This is due to the requirement that M-code in DirectQuery has to be translated into SQL at runtime.
Query Folding
Query Folding is the ability for a Power Query to be transformed into a single query at the data source (for example in Transact SQL). As such, Query Folding works like a traditional ETL process, and enables working on the data before loading. Query Folding is not always supported. Folding indicators (such as folding, not folding, might fold, opaque, unknown) might indicate up to which step a query might fold. Non-folding queries will have to be performed on the client-side. The order of queries can determine how many of the steps which get folded.
See also
*
Online analytical processing
Online analytical processing, or OLAP (), is an approach to answer multi-dimensional analytical (MDA) queries swiftly in computing. OLAP is part of the broader category of business intelligence, which also encompasses relational databases, repo ...
(OLAP)
*
Data Analysis Expressions (DAX), a complementary expression language used to query models and compute advanced measures
*
Logical data model A logical data model or logical schema is a data model of a specific problem domain expressed independently of a particular database management product or storage technology (physical data model) but in terms of data structures such as relational ta ...
, hereunder
star schema
In computing, the star schema is the simplest style of data mart schema and is the approach most widely used to develop data warehouses and dimensional data marts. The star schema consists of one or more fact tables referencing any number of dim ...
with
fact table
In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is located at the center of a star schema or a snowflake schema surrounded by dimension tables. Where multiple fact tables are used, these a ...
and
dimension tables
*
Data blending Data blending is a process whereby big data from multiple sources are merged into a single data warehouse or data set. It concerns not merely the merging of different file formats or disparate sources of data but also different varieties of data. Da ...
References
Microsoft Office