Power Query is a technology embedded in Excel and other Microsoft products designed to help you shape your data. In Excel, select the Data tab on the ribbon to see the Get & Transform Data and Queries & Connections groups.
After you import and refresh data from a wide variety of data sources, you can then shape the data in a step-by-step transformation that gradually creates a unique, tabular shape to fit your data analysis needs.
We recommend you make this help topic a browser favorite that you can frequently use to easily find the Excel Power Query information you seek and corresponding help information in the Power Query documentation help site.
Concepts and tutorials
There's a lot to Power Query. Start here to get the big picture and develop the skills you need to become proficient at it.
- Microsoft Power Query product page
- About Power Query in Excel
- Get to know Power Query
- Learn to combine multiple data sources
- What is Power Query? (docs.com)
- Tutorial: Shape and combine data using Power Query (docs.com)
- Automate data cleaning with Power Query (docs.com)
Community sites
Follow like-minded folks who can often answer questions for you.
Import and refresh data
Excel has always been a rich platform for safely importing and connecting to a wide variety of data sources that you can analyze and refresh to get up-to-date information.
- Import data from external data sources
- Import data from the web
- Import data from a folder with multiple files
- Refresh an external data connection
- How data journeys through Excel
- Handling data source errors
- Import Data from Database using Native Database Query
- Manage data source settings and permissions
- Getting data (docs.com)
- Import data using native database query (docs.com)
- Dealing with errors (docs.com)
Create, load, or edit a query
The most common way to create a query is to import data. After you shape your data in Power Query, you often load it into a worksheet or Data Model. Periodically, you may want to edit the query.
Connections and properties
External data sources have connection information and data properties that you sometimes need to view and update.
- Create, edit, and manage connections to external data
- Manage external data ranges and their properties
- Connection properties
- Connect to (Import) external data
Transform and shape data: columns
When shaping data, you can perform many tasks on columns, such as adding, removing, renaming, splitting, and expanding them.
Beginning
- Rename a column
- Add an index column
- Remove columns
- Choose or remove columns (docs.com)
- Split a column of text
- Merge columns
- Add or change data types
Advanced
- Add a column from an example
- Add a column from examples (docs.com)
- Add a conditional column
- Add a conditional column (docs.com)
- Add a custom column
- Add a custom column (docs.com)
- Work with a List, Record, or Table structured column
- Parse text as JSON or XML
- Pivot columns
- Pivot column (docs.com)
- Unpivot columns
- Unpivot column (docs.com)
Transform and shape data: rows
When shaping data, you can perform many tasks on rows, such as promoting or demoting column headers, removing, adding, and correcting them.
- Promote or demote rows and column headers
- Promote or demote column headers (docs.com)
- Remove or keep rows with errors
- Replace values
- Replace values and errors (docs.com)
- Keep or remove duplicate rows
- Working with duplicates (docs.com)
Transform and shape data: sort and filter
Good news. You'll find that filtering and sorting data in Power Query is very similar to Excel.
- Filter data
- Filter by values in a column (docs.com)
- Filter a table by row position (docs.com)
- Sort data
- Sort columns (docs.com)
Transform and shape data: aggregate and combine
Sometimes you need to group data by a label, aggregate data with operations (Sum, Count, Average, Min, and Max), merge queries, and append queries.
- Combine multiple queries
- Merge queries
- Merge queries overview (docs.com)
- Append queries
- Append queries (docs.com)
- Create a fuzzy match
- Fuzzy merge (docs.com)
Using Query Editor and managing queries
Although most of the transformations you make when you shape data can easily be done in the user interface, the Query Editor is also a way to fine-tune your transformations, and when necessary, add formulas to solve complex issues.
- Create Power Query formulas
- Create and invoke a custom function
- Manage queries
- Manage Preview Data
- Profile data to view statistics
- Using the Applied Steps list (docs.com)
- Using custom functions (docs.com)
- Power Query M function reference (docs.com)
Excel for Mac
You can import and refresh data files and databases on the Mac.
Excel for the web
You can import, edit, and refresh data from multiple sources on Excel for the web, using the full Power Query experience.
Advanced
Power Query is a mature technology and the following help topics show how to perform advanced operations.
- Create a parameter query
- Create a data type
- Load blobs and images into a Data Model
- Learn to use Power Query and Power Pivot in Excel
- How Power Query and Power Pivot work together
- Set privacy levels
- Set a locale or region for data
Reference
These help topics provide the reference information you may need along your data-shaping journey.