User:Stracci~909/sandbox

Power Query is an ETL tool created by Microsoft and is available in several variations within software including Microsoft Excel, Microsoft Power BI, SQL Server Analysis Services, Dataverse,, Azure Data Factory, and Microsoft Dynamics 365. Power Query provides a GUI which allows users to perform many common data preparation operations without needing to write code. More advanced operations may be performed through expressions written in the M formula language. Power Query exists in two forms which provide similar experiences: Power Query Online which is utilized in online cloud-based platforms, and Power Query for Desktop which is used in desktop applications including Excel and Power BI.

ETL is closely related to data modeling, 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.

Potential sources: https://link.springer.com/book/10.1007/978-1-4842-6018-0

https://www.microsoft.com/en-us/research/wp-content/uploads/2016/04/FKPowerPivot.pdf

https://ceur-ws.org/Vol-3462/POLY1.pdf

https://link.springer.com/book/10.1007/978-1-4842-8048-5

https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let

History
Power Query was first announced in 2011 under the codename "Data Explorer" as part of Azure SQL Labs. In 2013, in order to expand on the self-service business intelligence capabilities of Microsoft Excel, the project was redesigned to be packaged as an add-in Excel and was renamed "Data Explorer Preview for Excel", and was made available for Excel 2010 and Excel 2013. In July 2013 the add-in was removed from preview and renamed to "Power Query". Monthly updates for the add-in were released until 2016, when Power Query was included in Excel natively. In Excel 2016, the function was renamed "Get & Transform" for a short time, but has since been changed back to Power Query.

In April 2017, Power Query was made available in Microsoft Analysis Services. With the launch of the Common Data Service in March 2018, Power Query was included as its main data import tool.

Features
Each query in Power Query is composed of a series of expressions (called "steps") recorded in the M formula language. Much of the user interaction with Power Query can be done via graphical user interfaces with wizards to accomplish common or basic tasks. These interactions generate M code behind the scenes which may be further customized in the Advanced Editor or formula bar. M code may also be written directly in the Advanced Editor, allowing for greater expressive power than what is possible with just the user interface interactions.

M Formula language
Power Query is built on what was then a new query 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 is described by Microsoft as a "mostly pure, higher-order, dynamically typed, partially lazy, functional language." The M language is case-sensitive. Comments are written in C-style, with inline comments beginning with two forward slashes, and block comments delimited by  and.

The M engine is the underlying query execution engine that processes queries generated in the M formula language. The language includes a common library of functions and operators that can be used against the data types within an expression.

Expressions
Expressions are the central construct of the language. Expressions perform operations and are evaluated to yield a single value. Expressions support the creation of variables,

Once created, values are immutable, with the exception of lists, tables, and binary values, which are re-created on demand when requested.

Let expression
An M query commonly consists of a let expression where values 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.

Custom Functions
Custom functions can be defined for re-use within a query. Functions are defined first by a list of arguments then by one or more expressions. All arguments are required by default; using the optional keyword will make the argument optional They can be defined inline within a larger query or separately as their own query.

Data Types
The M language includes definitions for primitive values and structured values.

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