User:Stracci~909/sandbox
 | This article has multiple issues. Please help improve it or discuss these issues on the talk page. (Learn how and when to remove these template messages)
|
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,[1], 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,[2] 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[edit]
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"[3], and was made available for Excel 2010 and Excel 2013.[4] In July 2013 the add-in was removed from preview and renamed to "Power Query"[5]. 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"[6] 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[edit]
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[edit]
Power Query is built on what was then[when?] 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 */.[7]
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[edit]
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[edit]
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.
let
x = 20,
y = 10,
Result = x * y
in
Result
Custom Functions[edit]
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
(x, a, optional b) =>
let
Result = (x * a) + b
in
Result
They can be defined inline within a larger query or separately as their own query.
Data Types[edit]
The M language includes definitions for primitive values and structured values.
Primitive data types[edit]
| Type | Implementation | Range |
|---|---|---|
| Binary | ||
| Date | Number of days | 0 (January 1, 1970), to 3,652,058 (December 31, 9999) |
| Duration | Numeric difference between two dates or times, with 100 nanosecond resolution. | +/- 10,675,199d 2h 48m 5.4775808s |
| Logical | Boolean value | |
| Null | Represents an absence of a value. null is both a type and a value. | |
| Number | At least the precision of an IEEE 64-bit double) | |
| Text | A string of arbitray unicode characters | |
| Time | Number of 100-nanosecond ticks since midnight | |
| Table | Tables are the most common types. Many of the ribbon commands represented by steps in the query are translated to table types. |
Structured data types[edit]
| Type | Description | ||
|---|---|---|---|
| List | A zero-based sequence of values | ||
| Record | A set of fields represented by key/value pairs | ||
| Table | A set of values organized into named rows and columns. |
DirectQuery[edit]
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[edit]
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.
See also[edit]
- Online analytical processing (OLAP)
- Data Analysis Expressions (DAX), a complementary expression language used to query models and compute advanced measures
- Logical data model, hereunder star schema with fact table and dimension tables
References[edit]
- ^ Klopfenstein, Doug. "Power Query documentation - Power Query". Power Query documentation. Retrieved 2024-05-14.
- ^ Dearmer, Abe. "Why ETL Data Modeling is Critical in 2021". Retrieved 2022-10-27.
- ^ Raviv, Gil (2019). Collect, combine, and transform data using Power Query in Excel and Power BI. United States: published with the authorization of Microsoft Corporation by Pearson Education, Inc. ISBN 978-1-5093-0795-1. OCLC 1006311144.
- ^ kexugit (2013-02-27). "Announcing Microsoft "Data Explorer" Preview for Excel". learn.microsoft.com. Retrieved 2024-05-14.
- ^ kexugit (2013-07-06). "'Data Explorer' is now Microsoft Power Query for Excel". learn.microsoft.com. Retrieved 2024-05-15.
- ^ Team, The Microsoft 365 Marketing (2015-09-10). "Integrating Power Query technology in Excel 2016". Microsoft 365 Blog. Retrieved 2024-05-17.
{{cite web}}: CS1 maint: numeric names: authors list (link) - ^ "Microsoft Power Query M Formula Language Specification" (PDF). July 2019. Archived from the original on 2022-08-08. Retrieved 2024-05-14.