Power Query for Excel Help

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)

Follow like-minded folks who can often answer questions for you.

• The Microsoft Excel Community

• Microsoft 365 and Excel Answers Community

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 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)

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.

• Create, load, or edit a query in Excel

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

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)

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)

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)

Sometimes you need to group data by a label, aggregate data with operations (Sum, Count, Average, Min, and Max), merge queries, and append queries.

• Group rows of data

• Grouping or summarizing rows (docs.com)

• Aggregate data from a column

• Combine multiple queries

• Merge queries

• Merge queries overview (docs.com)

• Append queries

• Append queries (docs.com)

• Create a fuzzy match

• Fuzzy merge (docs.com)

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)

You can import and refresh data files and databases on the Mac.

•   Use Power Query in Excel for Mac

•   Import data from a database in Excel for Mac

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

These help topics provide the reference information you may need along your data-shaping journey.

• Power Query data sources in Excel versions

• Power Query specifications and limits

• Power Query online limits (docs.com)

• Microsoft Power Query Terms of Service