Power Query is an ETL tool created by

Microsoft Microsoft Corporation is an American multinational corporation, multinational technology company, technology corporation producing Software, computer software, consumer electronics, personal computers, and related services headquartered at th ...

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 usu ...

, 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 * Tran ...

, 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, 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, Analysis Services,

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, Azure Data Factory, SSIS, Dynamics 365, and in cloud services such as

Microsoft Dataflows Microsoft Corporation is an American multinational corporation, multinational technology company, technology corporation producing Software, computer software, consumer electronics, personal computers, and related services headquartered at th ...

, including Power BI Dataflow used with the online Power BI Service or the somehwat more generic version of Microsoft Dataflow used with Power Automate. 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 su ...

, 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 c ...

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 A pivot table is a table of grouped values that aggregates the individual items of a more extensive table (such as from a database, spreadsheet, or business intelligence program) within one or more discrete categories. This summary might include ...

, 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.

References