What is Power Query?

Power Query is a data transformation and data preparation engine. It includes a graphical interface for getting data from sources and a Power Query editor for applying transformations. Power Query transforms data but doesn't provide a single storage destination; the product or service that hosts Power Query determines where the results are loaded. By using Power Query, you can perform the extract, transform, and load (ETL) processing of data.

How Power Query helps with data acquisition

Power Query helps you connect to data, reshape it, and save the transformation steps as a repeatable query that you can refresh. The following table maps common data preparation challenges to these capabilities.

Power Query Online and Desktop experiences

You can use Power Query without writing code. The Power Query editor helps you apply transformations by interacting with ribbons, menus, buttons, and other interface components. When you create a transformation step in the editor, Power Query automatically creates the M code required for the transformation.

The Power Query editor is the primary data preparation experience. In the editor, you can connect to a wide range of data sources and apply hundreds of data transformations by previewing data and selecting transformations from the UI. These data transformation capabilities are common across all data sources, whatever the underlying data source limitations.

Currently, two Power Query experiences are available:

• Power Query Online: Available in integrations such as Power BI dataflows and Microsoft Power Platform dataflows that provide the experience through a webpage.
• Power Query Desktop: Available in desktop products such as Excel and Power BI Desktop.

Transform data with Power Query

The transformation engine in Power Query includes many prebuilt transformation functions that you can use through the graphical interface of the Power Query editor. Power Query records transformations as query steps and applies them when the query runs; it doesn't modify the source data. Transformations can be as simple as removing a column or filtering rows, or as common as using the first row as a table header. Advanced transformation options include merge, append, group by, pivot, and unpivot.

You can apply all these transformations by choosing the transformation option in the menu, and then applying the options required for that transformation. The following illustration shows a few of the transformations available in the Power Query editor.

More information: The Power Query user interface

Use Power Query with dataflows

Dataflows provide a cloud-based Power Query experience for reusable data preparation. In products that support dataflows, you can connect to data, apply Power Query transformations, and store the prepared output for use by other processes, products, or services. Available destinations and refresh capabilities depend on the product that hosts the dataflow. For example, a dataflow can store output in Microsoft Dataverse or Azure Data Lake Storage instead of loading it directly into Excel or Power BI.

More information: What are dataflows?

Power Query M formula language

Some data transformations require special configurations and settings that the graphical interface doesn't currently support. The Power Query engine uses a scripting language behind the scenes for all Power Query transformations: the Power Query M formula language, also known as M.

The M language is the data transformation language of Power Query. Anything that happens in the query is ultimately written in M. If you want to do advanced transformations by using the Power Query engine, use the Advanced Editor to access the script of the query and modify it as needed. If you find that the user interface functions and transformations can't perform the exact changes you need, use the Advanced Editor and the M language to fine-tune your functions and transformations.

The following M query retrieves email attachments from Microsoft Exchange, filters messages by attachment status, subject, and folder, invokes a custom transformation function for each attachment, and assigns data types to the output columns. The example shows how a sequence of Power Query transformations is represented in M and edited in the Advanced Editor.

let
    Source = Exchange.Contents("xyz@contoso.com"),
    Mail1 = Source{[Name="Mail"]}[Data],
    #"Expanded Sender" = Table.ExpandRecordColumn(Mail1, "Sender", {"Name"}, {"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Sender", each ([HasAttachments] = true)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Subject] = "sample files for email PQ test") and ([Folder Path] = "\Inbox\")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Attachments"}),
    #"Expanded Attachments" = Table.ExpandTableColumn(#"Removed Other Columns", "Attachments", {"Name", "AttachmentContent"}, {"Name", "AttachmentContent"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Expanded Attachments", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Mail", each #"Transform File from Mail"([AttachmentContent])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Mail"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Mail", Table.ColumnNames(#"Transform File from Mail"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}})
in
    #"Changed Type"

More information: Power Query M formula language

Where can you use Power Query?

The following table lists Microsoft products and services where you can find Power Query.

Related content

• Connectors in Power Query
• Getting data overview
• The Power Query user interface
• What are dataflows?