Menu
Home
People
Places
Arts
History
Plants & Animals
Science
Life & Culture
Technology
Reference.org
Power Query
open-in-new
<h2 id="history">History</h2> <p>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 <a href="/facts/Business_intelligence/ftThn7Wf">business intelligence</a> capabilities of Microsoft Excel, the project was redesigned to be packaged as an add-in Excel and was renamed "Data Explorer Preview for Excel",<a class="footnote-ref" id="fnref:4" href="#fn:4"><sup>4</sup></a> and was made available for Excel 2010 and Excel 2013.<a class="footnote-ref" id="fnref:5" href="#fn:5"><sup>5</sup></a> In July 2013 the add-in was removed from preview and renamed to "Power Query".<a class="footnote-ref" id="fnref:6" href="#fn:6"><sup>6</sup></a><a class="footnote-ref" id="fnref:7" href="#fn:7"><sup>7</sup></a> 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"<a class="footnote-ref" id="fnref:8" href="#fn:8"><sup>8</sup></a> for a short time, but has since been changed back to Power Query. </p><p>In April 2017, Power Query was made available in <a href="/facts/Microsoft_Analysis_Services/92MhPt5Q">Microsoft Analysis Services</a>. With the launch of the <a href="/facts/Microsoft_Power_Platform/Izqae1tn">Common Data Service</a> in March 2018, Power Query was included as its main data import tool. </p> <h2 id="m-formula-language">M Formula language</h2> <p>Power Query is built on what was then[<i>when?</i>] a new <a href="/facts/Query_language/Sa6K4xXM">query language</a> called M. It is a <a href="/facts/Mashup_(web_application_hybrid)/gn6mpfH9">mashup language</a> (hence the letter M) designed to create queries that mix together data. It is similar to the <a href="/facts/F_Sharp_(programming_language)/jdXHnYz5">F#</a> 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. </p><p>Much of the user interaction with Power Query can be done via <a href="/facts/Graphical_user_interface/wBlQFWDP">graphical user interfaces</a> with <a href="/facts/Wizard_(software)/8c4Y3BQ0">wizards</a>, 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. </p> <h3>Let expression</h3> <p>User queries are typically written with a top level <i>let</i> expression. The <i>let</i> expression contains a list of comma-separated named reference (variables) bindings and an <i>in</i> expression which is what the <i>let</i> expression evaluates to. The <i>in</i> expression can reference the variables and the variables can reference each other. Backwards and forward referencing is allowed, and self-referencing is allowed by prefixing the <i>@</i> on the variable. Variables are recursively evaluated as needed to evaluate the <i>in</i> expression. No variable is evaluated more than once. </p> <h4>Examples</h4> let a = "Hello", b = "World", result = a & " " & b in result let result = Fib(5), Fib = (iteration) => if iteration = 0 or iteration = 1 then 1 else let a = @Fib(iteration - 1), b = @Fib(iteration - 2) in a + b in result <h3>Assertions and datatypes</h3> <p>Variables are not typed in Power Query. Instead, an expression can have a type assertion which will evaluate to an error when the expression does not evaluate to a value compatible with the assertion. Assertions can be preceded by <i>nullable</i> to include null in the allowed values. </p> Datatypes/Assertions<table><tbody><tr><th>Name</th><th>Description</th><th>Datatype</th><th>Assertion</th></tr><tr><td>number</td><td>Assertion for integer and floating-point numbers</td><td> No</td><td> Yes</td></tr><tr><td>int</td><td>Signed 32-bit integer</td><td> Yes</td><td> No</td></tr><tr><td>long</td><td>Signed 64-bit integer</td><td> Yes</td><td> No</td></tr><tr><td>double</td><td>IEEE 754 float</td><td> Yes</td><td> No</td></tr><tr><td>decimal</td><td>128-bit float. Same as C#'s decimal</td><td> Yes</td><td> No</td></tr><tr><td>time</td><td>Time of day</td><td> Yes</td><td> Yes</td></tr><tr><td>date</td><td>A calendar date ranging from 1 CE to 9999 CE in the Georgian Calendar</td><td> Yes</td><td> Yes</td></tr><tr><td>datetime</td><td>A composite of the date and time datatypes</td><td> Yes</td><td> Yes</td></tr><tr><td>duration</td><td>A measurement of elapsed time (can be negative)</td><td> Yes</td><td> Yes</td></tr><tr><td>logical</td><td>Represents a Boolean <i>true</i> or <i>false</i> value</td><td> Yes</td><td> Yes</td></tr><tr><td>text</td><td>A Unicode string</td><td> Yes</td><td> Yes</td></tr><tr><td>guid</td><td>A Globally Unique Identifier (Converts to a text as needed automatically)</td><td> Yes</td><td> No</td></tr><tr><td>list</td><td>An ordered list of values</td><td> Yes</td><td> Yes</td></tr><tr><td>record</td><td>An ordered map from text to any value</td><td> Yes</td><td> Yes</td></tr><tr><td>table</td><td>A 2D matrix where each column has a unique name and type (type not checked on table contents)</td><td> Yes</td><td> Yes</td></tr><tr><td>function</td><td>A power query function</td><td> Yes</td><td> Yes</td></tr><tr><td>type</td><td>Represents a datatype and may contain assertion information</td><td> Yes</td><td> Yes</td></tr><tr><td>action</td><td>An internally used datatype</td><td> Yes</td><td> Yes</td></tr><tr><td>null</td><td>The null singleton</td><td> Yes</td><td> Yes</td></tr><tr><td>any</td><td>Represents all values</td><td> No</td><td> Yes</td></tr><tr><td>anynonnull</td><td>Represents all values except null</td><td> No</td><td> Yes</td></tr><tr><td>none</td><td>Represents no values and always fails as an assertion</td><td> No</td><td> Yes</td></tr><tr><td>error</td><td>A pseudo value representing an error</td><td> No</td><td> No</td></tr></tbody></table> <h3>Comments</h3> <p>Power Query supports the C block (<i>/* ... */</i>) and C line (<i>// ...</i>) comments. </p> <h3>DirectQuery</h3> <p>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 from DAX into SQL and then into the data source's native query language (if it isn't SQL) upon user interaction with the data. </p> <h3>Query Folding</h3> <p>Query Folding is the ability for the Power Query steps 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. Steps like filtering, selecting columns and simple SQL arithmetic are supported. Steps like creating index and appending or merging non foldable sources with foldable sources are not. 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. </p> <h3>Connectors</h3> <p>A connector in power query is a library which enables queries to connect to a datasource. End users of Power BI can write custom connectors in the Power Query language. If a user is the owner/maintainer of the datasource technology they can request Microsoft certify their connector and include it in future releases of Power Bi after the review of the code. Connectors that are not certified can still be shared with other people who enable developer mode in Power Bi, or by Power Bi online domain administrators. </p> <h2 id="see-also">See also</h2> <ul><li><a href="/facts/Online_analytical_processing/VNNbIlD0">Online analytical processing</a> (OLAP)</li> <li><a href="/facts/Data_Analysis_Expressions/9K5BjR9f">Data Analysis Expressions</a> (DAX), a complementary expression language used to query models and compute advanced measures</li> <li><a href="/facts/Logical_data_model/K0EbrogL">Logical data model</a>, hereunder <a href="/facts/Star_schema/hXsGMSKf">star schema</a> with <a href="/facts/Fact_table/3xlrQLkA">fact table</a> and <a href="/facts/Dimension_table/TQQANKl1">dimension tables</a></li></ul> <h2 id="external-links">External links</h2> <ul><li><a href="https://learn.microsoft.com/en-us/power-query/power-query-what-is-power-query">What is Power Query?</a> - Microsoft Learn</li> <li><a href="https://learn.microsoft.com/en-us/powerquery-m/">Power Query formula language</a></li></ul> <h2 id="references">References</h2> <ol> <li id="fn:1"><p>DougKlopfenstein. "Power Query documentation - Power Query". Retrieved 2022-10-27. <a href="https://learn.microsoft.com/en-us/power-query/" target="_blank">https://learn.microsoft.com/en-us/power-query/</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li> <li id="fn:2"><p>ptyx507x. "What is Power Query? - Power Query". Retrieved 2022-10-27.{{cite web}}: CS1 maint: numeric names: authors list (link) <a href="https://learn.microsoft.com/en-us/power-query/power-query-what-is-power-query" target="_blank">https://learn.microsoft.com/en-us/power-query/power-query-what-is-power-query</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li> <li id="fn:3"><p>Dearmer, Abe. "Why ETL Data Modeling is Critical in 2021". Retrieved 2022-10-27. <a href="https://www.integrate.io/blog/etl-data-modeling-why-is-it-important/" target="_blank">https://www.integrate.io/blog/etl-data-modeling-why-is-it-important/</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li> <li id="fn:4"><p>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. <a href="978-1-5093-0795-1" target="_blank">978-1-5093-0795-1</a> <a href="#fnref:4" class="footnote-back-ref">↩</a></p></li> <li id="fn:5"><p>Webb, Chris (2013-02-27). "Public Preview of Data Explorer". Chris Webb's BI Blog. Retrieved 2025-01-27. <a href="https://blog.crossjoin.co.uk/2013/02/27/public-preview-of-data-explorer/" target="_blank">https://blog.crossjoin.co.uk/2013/02/27/public-preview-of-data-explorer/</a> <a href="#fnref:5" class="footnote-back-ref">↩</a></p></li> <li id="fn:6"><p>kexugit (2013-07-06). "'Data Explorer' is now Microsoft Power Query for Excel". learn.microsoft.com. Retrieved 2024-05-15. <a href="https://learn.microsoft.com/en-us/archive/blogs/dataexplorer/data-explorer-is-now-microsoft-power-query-for-excel" target="_blank">https://learn.microsoft.com/en-us/archive/blogs/dataexplorer/data-explorer-is-now-microsoft-power-query-for-excel</a> <a href="#fnref:6" class="footnote-back-ref">↩</a></p></li> <li id="fn:7"><p>Jackson, Joab (2013-07-08). "Microsoft adds business intelligence tools to Office 365". PCWorld. Retrieved 2025-01-27. <a href="https://www.pcworld.com/article/452789/microsoft-adds-business-intelligence-to-office-365.html" target="_blank">https://www.pcworld.com/article/452789/microsoft-adds-business-intelligence-to-office-365.html</a> <a href="#fnref:7" class="footnote-back-ref">↩</a></p></li> <li id="fn:8"><p>"Integrating Power Query technology in Excel 2016". Microsoft 365 Blog. 2015-09-10. Retrieved 2024-05-17. <a href="https://www.microsoft.com/en-us/microsoft-365/blog/2015/09/10/integrating-power-query-technology-in-excel-2016/" target="_blank">https://www.microsoft.com/en-us/microsoft-365/blog/2015/09/10/integrating-power-query-technology-in-excel-2016/</a> <a href="#fnref:8" class="footnote-back-ref">↩</a></p></li> </ol>