learn.microsoft.com Open in urlscan Pro
23.50.126.168  Public Scan

Form analysis
3 forms found in the DOM

Name: site-header-search-form-mobile — GET /en-ca/search/

<form class="flex-grow-1" method="GET" role="search" id="ms--site-header-search-form-mobile" data-bi-name="site-header-search-form-mobile" name="site-header-search-form-mobile" aria-label="Search" action="/en-ca/search/">
  <div class="autocomplete display-block" data-bi-name="autocomplete"><!---->
    <div class="field-body control ">
      <input role="combobox" maxlength="100" aria-autocomplete="list" autocapitalize="off" autocomplete="off" autocorrect="off" spellcheck="false" id="site-header-search-autocomplete-input-mobile"
        data-test-id="site-header-search-autocomplete-input-mobile" class="autocomplete-input input 
						
						width-full" type="search" name="terms" aria-expanded="false" aria-owns="ax-1-listbox" aria-controls="ax-1-listbox" aria-activedescendant="" aria-label="Search" aria-describedby="ms--site-header-search-autocomplete-input-mobile-description"
        placeholder="Search" data-bi-name="site-header-search-autocomplete-input-mobile" pattern=".*">
      <span aria-hidden="true" class="autocomplete-loader loader has-text-primary " hidden=""></span>
      <span hidden="" id="ms--site-header-search-autocomplete-input-mobile-description"> Suggestions will filter as you type </span>
    </div>
    <ul role="listbox" id="ax-1-listbox" data-test-id="site-header-search-autocomplete-input-mobile-listbox" class="autocomplete-suggestions is-vertically-scrollable padding-xxs " aria-label="Suggestions" hidden="">
    </ul>
    <!---->
  </div>
  <!-- mobile safari will not dispatch submit event unless there's a submit button that is not display:none -->
  <button type="submit" class="visually-hidden" tabindex="-1" aria-hidden="true"></button>
  <input name="category" hidden="" value="">
</form>

Name: site-header-search-form — GET /en-ca/search/

<form class="flex-grow-1" method="GET" role="search" id="ms--site-header-search-form" data-bi-name="site-header-search-form" name="site-header-search-form" aria-label="Search" action="/en-ca/search/">
  <div class="autocomplete display-block" data-bi-name="autocomplete"><!---->
    <div class="field-body control ">
      <input role="combobox" maxlength="100" aria-autocomplete="list" autocapitalize="off" autocomplete="off" autocorrect="off" spellcheck="false" id="site-header-search-autocomplete-input" data-test-id="site-header-search-autocomplete-input" class="autocomplete-input input input-sm
						
						width-full" type="search" name="terms" aria-expanded="false" aria-owns="ax-0-listbox" aria-controls="ax-0-listbox" aria-activedescendant="" aria-label="Search" aria-describedby="ms--site-header-search-autocomplete-input-description"
        placeholder="Search" data-bi-name="site-header-search-autocomplete-input" pattern=".*">
      <span aria-hidden="true" class="autocomplete-loader loader has-text-primary " hidden=""></span>
      <span hidden="" id="ms--site-header-search-autocomplete-input-description"> Suggestions will filter as you type </span>
    </div>
    <ul role="listbox" id="ax-0-listbox" data-test-id="site-header-search-autocomplete-input-listbox" class="autocomplete-suggestions is-vertically-scrollable padding-xxs " aria-label="Suggestions" hidden="">
    </ul>
    <!---->
  </div>
  <!-- mobile safari will not dispatch submit event unless there's a submit button that is not display:none -->
  <button type="submit" class="visually-hidden" tabindex="-1" aria-hidden="true"></button>
  <input name="category" hidden="" value="">
</form>

javascript:

<form action="javascript:" role="search" aria-label="Search" class="margin-bottom-xxs"><label class="visually-hidden" for="ax-2">Search</label>
  <div class="autocomplete display-block" data-bi-name="autocomplete"><!---->
    <div class="field-body control has-icons-left">
      <input role="combobox" maxlength="100" aria-autocomplete="list" autocapitalize="off" autocomplete="off" autocorrect="off" spellcheck="false" id="ax-2" data-test-id="ax-2" class="autocomplete-input input input-sm
						control has-icons-left
						width-full" type="text" aria-expanded="false" aria-owns="ax-3-listbox" aria-controls="ax-3-listbox" aria-activedescendant="" aria-describedby="ms--ax-2-description" placeholder="Filter by title" pattern=".*">
      <span aria-hidden="true" class="icon is-small is-left">
        <span class="has-text-primary docon docon-filter-settings"></span>
      </span>
      <span aria-hidden="true" class="autocomplete-loader loader has-text-primary " hidden=""></span>
      <span hidden="" id="ms--ax-2-description"> Suggestions will filter as you type </span>
    </div>
    <ul role="listbox" id="ax-3-listbox" data-test-id="ax-2-listbox" class="autocomplete-suggestions is-vertically-scrollable padding-xxs " aria-label="Suggestions" hidden="">
    </ul>
    <!---->
  </div>
</form>

Text Content

Skip to main content

We use optional cookies to improve your experience on our websites, such as
through social media connections, and to display personalized advertising based
on your online activity. If you reject optional cookies, only cookies necessary
to provide you the services will be used. You may change your selection by
clicking “Manage Cookies” at the bottom of the page. Privacy Statement
Third-Party Cookies

Accept Reject Manage cookies


AI SKILLS CHALLENGE

Sep 24–Nov 1, 2024

Excel in AI technologies with Microsoft Copilot, Azure, and Fabric. Start the
challenge today.

Register now
Dismiss alert

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security
updates, and technical support.

Download Microsoft Edge More info about Internet Explorer and Microsoft Edge

Learn
Suggestions will filter as you type
Sign in


 * Profile
 * Settings

Sign out

Learn
   
 * Discover
      
    * Documentation
      
      In-depth articles on Microsoft developer tools and technologies
   
      
    * Training
      
      Personalized learning paths and courses
   
      
    * Credentials
      
      Globally recognized, industry-endorsed credentials
   
      
    * Q&A
      
      Technical questions and answers moderated by Microsoft
   
      
    * Code Samples
      
      Code sample library for Microsoft developer tools and technologies
   
      
    * Assessments
      
      Interactive, curated guidance and recommendations
   
      
    * Shows
      
      Thousands of hours of original programming from Microsoft experts
   
      
   
   Microsoft Learn for Organizations
   
   Boost your team's technical skills
   
   Access curated resources to upskill your team and close skills gaps.

   
 * Product documentation
      
    * ASP.NET
      
    * Azure
      
    * Dynamics 365
      
    * Microsoft 365
      
    * Microsoft Edge
      
    * Microsoft Entra
      
    * Microsoft Graph
      
    * Microsoft Intune
      
    * Microsoft Purview
      
    * Microsoft Teams
      
    * .NET
      
    * Power Apps
      
    * Power Automate
      
    * Power BI
      
    * Power Platform
      
    * PowerShell
      
    * SQL
      
    * Sysinternals
      
    * Visual Studio
      
    * Windows
      
    * Windows Server
      
   
   View all products
   
   Microsoft Learn for Organizations
   
   Boost your team's technical skills
   
   Access curated resources to upskill your team and close skills gaps.

   
 * Development languages
      
    * C++
      
    * C#
      
    * DAX
      
    * Java
      
    * OData
      
    * OpenAPI
      
    * Power Query M
      
    * VBA
      
   
   Microsoft Learn for Organizations
   
   Boost your team's technical skills
   
   Access curated resources to upskill your team and close skills gaps.

   
 * Topics
      
    * Artificial intelligence
      
    * Compliance
      
    * DevOps
      
    * Platform engineering
      
    * Security
      
   
   Microsoft Learn for Organizations
   
   Boost your team's technical skills
   
   Access curated resources to upskill your team and close skills gaps.

   

Suggestions will filter as you type
Sign in


 * Profile
 * Settings

Sign out
Power Platform
   
 * Get started
     
   * Admin guide
     
   * ALM guide
     
   * Developer guide
     
   * Training
     
   
 * Products
     
   * Power Apps
     
   * Power Automate
     
   * Power BI
     
   * Power Pages
     
   * Copilot Studio
     
   
 * Guidance
   
 * Troubleshooting
     
   * Get help and support
     
   * Troubleshooting articles
     
   
 * Release plans
   
 * Resources
     
   * Community
     
   * Blog
     
   * Geographical availability
     
   * Language availability
     
   
 * More
     
   * Get started
       
     * Admin guide
       
     * ALM guide
       
     * Developer guide
       
     * Training
       
     
   * Products
       
     * Power Apps
       
     * Power Automate
       
     * Power BI
       
     * Power Pages
       
     * Copilot Studio
       
     
   * Guidance
     
   * Troubleshooting
       
     * Get help and support
       
     * Troubleshooting articles
       
     
   * Release plans
     
   * Resources
       
     * Community
       
     * Blog
       
     * Geographical availability
       
     * Language availability
       
     
   

Table of contents Exit focus mode

Search
Suggestions will filter as you type
 * Power Query documentation
 * What is Power Query?
 * Get data
 * Transform data
 * Dataflows
 * Power Query template (preview)
 * Keyboard shortcuts
 * Best practices
 * Feedback and support
 * Advanced topics
 * Connector reference
 * Support and troubleshooting
 * Create Power Query connectors
 * Resources

Download PDF
    
 1. Learn
    
    
 2. Power Platform
    
    
 3. Power Query
    

    
 1. Learn
    
    
 2. Power Platform
    
    
 3. Power Query
    

Read in English Save
 * Add to Collections
 * Add to Plan

Table of contents Read in English Add to Collections Add to Plan Edit


--------------------------------------------------------------------------------

SHARE VIA

Facebook x.com LinkedIn Email

--------------------------------------------------------------------------------

Print
Table of contents


WHAT IS POWER QUERY?

 * Article
 * 2024-01-24
 * 13 contributors

Feedback


IN THIS ARTICLE

    
 1. How Power Query helps with data acquisition
    
 2. Power Query experiences
    
 3. Transformations
    
 4. Dataflows
    
 5. Power Query M formula language
    
 6. Where can you use Power Query?
    

Show 2 more

Power Query is a data transformation and data preparation engine. Power Query
comes with a graphical interface for getting data from sources and a Power Query
Editor for applying transformations. Because the engine is available in many
products and services, the destination where the data will be stored depends on
where Power Query was used. Using Power Query, you can perform the extract,
transform, and load (ETL) processing of data.



Diagram with symbolized data sources on the left, passing through Power Query
for transformation in the center, and then going to four destinations on the
right: Microsoft Azure Data Lake Storage, Microsoft Dataverse, Microsoft Excel
and Microsoft Power BI.




HOW POWER QUERY HELPS WITH DATA ACQUISITION

Business users spend up to 80 percent of their time on data preparation, which
delays the work of analysis and decision-making. Several challenges contribute
to this situation, and Power Query helps address many of them.

Expand table

Existing challenge How does Power Query help? Finding and connecting to data is
too difficult Power Query enables connectivity to a wide range of data sources,
including data of all sizes and shapes. Experiences for data connectivity are
too fragmented Consistency of experience, and parity of query capabilities over
all data sources. Data often needs to be reshaped before consumption Highly
interactive and intuitive experience for rapidly and iteratively building
queries over any data source, of any size. Any shaping is one-off and not
repeatable When using Power Query to access and transform data, you define a
repeatable process (query) that can be easily refreshed in the future to get
up-to-date data.
In the event that you need to modify the process or query to account for
underlying data or schema changes, you can use the same interactive and
intuitive experience you used when you initially defined the query. Volume (data
sizes), velocity (rate of change), and variety (breadth of data sources and data
shapes) Power Query offers the ability to work against a subset of the entire
data set to define the required data transformations, allowing you to easily
filter down and transform your data to a manageable size.
Power Query queries can be refreshed manually or by taking advantage of
scheduled refresh capabilities in specific products (such as Power BI) or even
programmatically (by using the Excel object model).
Because Power Query provides connectivity to hundreds of data sources and over
350 different types of data transformations for each of these sources, you can
work with data from any source and in any shape.


POWER QUERY EXPERIENCES

The Power Query user experience is provided through the Power Query Editor user
interface. The goal of this interface is to help you apply the transformations
you need simply by interacting with a user-friendly set of ribbons, menus,
buttons, and other interactive components.

The Power Query Editor is the primary data preparation experience, where you can
connect to a wide range of data sources and apply hundreds of different 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.

When you create a new transformation step by interacting with the components of
the Power Query interface, Power Query automatically creates the M code required
to do the transformation so you don't need to write any code.

Currently, two Power Query experiences are available:

 * Power Query Online—Found in integrations such as Power BI dataflows,
   Microsoft Power Platform dataflows, Azure Data Factory wrangling dataflows,
   and many more that provide the experience through an online webpage.
 * Power Query for Desktop—Found in integrations such as Power Query for Excel
   and Power BI Desktop.

Note

Although two Power Query experiences exist, they both provide almost the same
user experience in every scenario.


TRANSFORMATIONS

The transformation engine in Power Query includes many prebuilt transformation
functions that can be used through the graphical interface of the Power Query
Editor. These transformations can be as simple as removing a column or filtering
rows, or as common as using the first row as a table header. There are also
advanced transformation options such as merge, append, group by, pivot, and
unpivot.

All these transformations are made possible 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 Power Query Editor.



More information: Quickstart: Using Power Query in Power BI


DATAFLOWS

Power Query can be used in many products, such as Power BI and Excel. However,
using Power Query within a product limits its usage to only that specific
product. Dataflows are a product-agnostic service version of the Power Query
experience that runs in the cloud. Using dataflows, you can get data and
transform data in the same way, but instead of sending the output to Power BI or
Excel, you can store the output in other storage options such as Dataverse or
Azure Data Lake Storage. This way, you can use the output of dataflows in other
products and services.

More information: What are dataflows?


POWER QUERY M FORMULA LANGUAGE

In any data transformation scenario, there are some transformations that can't
be done in the best way by using the graphical editor. Some of these
transformations might 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 using the Power Query engine, you can use the Advanced Editor to
access the script of the query and modify it as you want. If you find that the
user interface functions and transformations won't perform the exact changes you
need, use the Advanced Editor and the M language to fine-tune your functions and
transformations.

Power Query M Copy


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 Power Query can
be found.

Expand table

Product M engine1 Power Query
Desktop2 Power Query
Online3 Dataflows4 Excel for Windows Yes Yes No No Excel for Mac Yes Yes No No
Power BI Yes Yes Yes Yes Power Apps Yes No Yes Yes Power Automate Yes No Yes No
Power BI Report Server Yes Yes No No Azure Data Factory Yes No Yes Yes Data
Factory in Microsoft Fabric Yes No Yes Yes SQL Server Integration Services Yes
No No No SQL Server Analysis Services Yes Yes No No Dynamics 365 Customer
Insights Yes No Yes Yes

Expand table

1M engine The underlying query execution engine that runs queries expressed in
the Power Query formula language ("M"). 2Power Query Desktop The Power Query
experience found in desktop applications. 3Power Query Online The Power Query
experience found in web browser applications. 4Dataflows Power Query as a
service that runs in the cloud and is product-agnostic. The stored result can be
used in other applications as services.


SEE ALSO

Data sources in Power Query
Getting data
Power Query quickstart
Shape and combine data using Power Query
What are dataflows





--------------------------------------------------------------------------------


FEEDBACK

Was this page helpful?

Yes No
Provide product feedback |
Ask the community

--------------------------------------------------------------------------------


ADDITIONAL RESOURCES



--------------------------------------------------------------------------------

Training

Module

Use Power Query to load data in Dataverse - Training

Learn how to synchronize data from different sources to a Microsoft Dataverse
table using Power Query and create dataflows in Power Apps.

Certification

Microsoft Certified: Power Platform Fundamentals - Certifications

Demonstrate the business value and product capabilities of Microsoft Power
Platform, such as Power Apps, data connections with Dataverse, and Power
Automate.



English (Canada)
California Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices
Theme
 * Light
 * Dark
 * High contrast

 * Manage cookies
 * Previous Versions
 * Blog
 * Contribute
 * Privacy
 * Terms of Use
 * Trademarks
 * © Microsoft 2024


ADDITIONAL RESOURCES



--------------------------------------------------------------------------------

Training

Module

Use Power Query to load data in Dataverse - Training

Learn how to synchronize data from different sources to a Microsoft Dataverse
table using Power Query and create dataflows in Power Apps.

Certification

Microsoft Certified: Power Platform Fundamentals - Certifications

Demonstrate the business value and product capabilities of Microsoft Power
Platform, such as Power Apps, data connections with Dataverse, and Power
Automate.




IN THIS ARTICLE



English (Canada)
California Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices
Theme
 * Light
 * Dark
 * High contrast

 * Manage cookies
 * Previous Versions
 * Blog
 * Contribute
 * Privacy
 * Terms of Use
 * Trademarks
 * © Microsoft 2024