Microsoft Excel Power Query: What It Does and How to Use It

Microsoft Excel Power Query is a built-in tool for importing data and transforming it into a clean, repeatable format. In Excel for Microsoft 365 on Windows, start from Data > Get Data to connect to a source, or select a range and choose Data > From Table/Range; make changes in the Power Query Editor, then load the result into Excel.

What Power Query is used for

Power Query helps you collect information from one or more sources, prepare it, and bring the result into a workbook. Its main value is that it records transformation steps. Instead of repeating the same manual cleanup every week, you can refresh the query and have Excel apply those steps to updated source data.

For example, a sales team might receive a separate CSV file from each region. Power Query can import the files, standardize column names, remove blank rows, set dates and amounts to the right data types, and combine the records into one table. If the next month’s files have the same structure, refresh can repeat that process.

Power Query is also called Get & Transform in Excel. It is designed for data preparation, not for replacing every Excel feature. After loading the result, you can use ordinary worksheet formulas, PivotTables, charts, or other tools to analyze it.

How the query workflow works

A query is a saved set of instructions. It does not usually overwrite the original source: it reads data from that source, applies its steps, and returns an output where you choose. A typical workflow is:

Connect: Choose a source, such as an Excel table, text file, folder, database, or supported online service.
Preview: Inspect the available data and select the table, sheet, or files you need.
Transform: Clean, filter, reshape, or combine the data in the editor.
Load: Send the result to a worksheet table or, where suitable, the workbook Data Model.
Refresh: Ask Excel to rerun the saved steps against the source’s current data.

In Excel for Windows, selecting Data > Get Data opens source choices and import options. To prepare an existing worksheet range, first make sure it has a header row, select a cell in it, and choose Data > From Table/Range. Excel may ask to create a table if the range is not already one. The Power Query Editor opens with a preview and a list of applied steps.

What you can do in the Power Query Editor

The editor presents transformations as commands, so many common tasks do not require writing code. For instance, you can filter out unwanted records, remove columns, split a column using a delimiter, replace values, remove duplicates, and change a column’s data type. Each action appears in the Applied Steps pane and can usually be edited or removed later.

Power Query can reshape data as well as clean it. A wide report with one column per month can be unpivoted into a simpler layout with a month column and a value column. Conversely, a pivot operation can turn values from one column into separate columns. These changes are useful because consistent, “one record per row” tables are easier to filter, summarize, and combine.

Data types deserve particular attention. A column that looks numeric may have been imported as text, and dates can be interpreted differently depending on regional settings. Set types deliberately and check the preview, especially for dates, decimal numbers, and identifiers. An account number that begins with zero, for example, should generally remain text so the leading zero is not lost.

A practical example: cleaning a monthly report

Imagine that each month you receive a CSV report containing transaction date, branch, item, quantity, and revenue. The file includes a title row above the column headers, some blank lines, and revenue values formatted with currency symbols. A repeatable Power Query process could look like this:

Import the CSV with Data > Get Data > From Text/CSV, then choose Transform Data to open the editor.
Remove the introductory title row so the actual field names become headers. Promote the first remaining row to headers if Excel has not done so automatically.
Remove blank rows and any columns that are not needed for the report.
Set transaction date to a date type, quantity to a whole-number type, and revenue to a decimal or currency-appropriate numeric type.
Filter out test records or rows with missing transaction dates, then load the cleaned table.

When the next report arrives, replace the source file while keeping its expected structure, then refresh the query. The transformation sequence runs again. If the incoming file changes its column names or layout, the query may need adjustment; refresh is repeatable, but it cannot infer every structural change.

Combining data: append versus merge

Two common operations have different purposes. Append stacks rows from tables with similar columns. It suits monthly files or regional reports that share the same fields. Merge matches rows from two tables using one or more key columns, similar to a database join. For example, merge a transactions table with a product list using Product ID to add product names or categories.

In the editor, use the Home tab’s Append Queries or Merge Queries commands. Before combining, check that matching columns have compatible names and data types. For a merge, verify that the chosen key is appropriate: if the lookup table contains duplicate keys, the result can produce more rows than expected. After a merge, expand only the fields you need from the matched table.

For recurring files in one folder, Excel’s folder connector can combine files that follow a consistent format. This is convenient for monthly exports, but it relies on compatible structures. A stray notes file, a renamed header, or a different sheet layout can disrupt the combine process, so keep the source folder organized.

Loading results and refreshing them

Choose Home > Close & Load to load a query using Excel’s default behavior, or use the Close & Load menu to select a destination and loading options. Depending on the query and workbook, you can load a table to a worksheet or create a connection and add data to the Data Model. A worksheet is convenient for direct inspection; the Data Model is useful when building relationships and larger PivotTable-based analyses.

To update results, select Data > Refresh All or right-click a loaded query table and choose Refresh. Refresh reruns the query; it does not necessarily happen as soon as a source file changes. You can also inspect query properties for refresh-related options, but availability can depend on the source and connection.

Keep the source accessible. A query connected to a local file can fail if the file is moved or renamed, and a network or online source may require credentials or access permissions. If other people will use the workbook, document where its source data lives and confirm that intended users can reach it.

Power Query, formulas, and PivotTables

Power Query and worksheet formulas solve different parts of a data task. Formulas calculate values in cells and can respond immediately to changed inputs. Power Query is better suited to repeatable import and cleanup steps, especially when the data arrives in files or external systems. A useful pattern is to prepare a clean table with Power Query, then use formulas or PivotTables to analyze it.

A PivotTable summarizes data that is already available; it does not normally replace the import-and-cleanup process. Power Query can prepare its source table, and a PivotTable can summarize the loaded result. Likewise, Power Query is not a live link that guarantees every change in the source appears instantly in the worksheet. The query must be refreshed, and the source must still be reachable.

Limitations, privacy, and common problems

Power Query is powerful, but it depends on source compatibility, file structure, permissions, and available memory. Large or complex queries may take time to refresh. Some connectors or capabilities differ by Excel edition, platform, organization settings, and the service being connected to. Excel for Microsoft 365 on Windows generally has the broadest desktop experience; Power Query support and editing options on Mac and Excel for the web differ and continue to evolve. Check the Data tab and Microsoft’s current support information for the specific source and operation you need.

When a query fails, look at the error details and identify the step where the problem begins. Frequent causes include:

Source path changed: Reconnect to the moved or renamed file, or restore it to the expected location.
Column not found: Update the query if the source renamed or removed a field referenced by a later step.
Type conversion error: Check for text, blanks, or unusual values in a column expected to contain dates or numbers.
Access or credential issue: Confirm permissions and sign in through Excel’s data-source or connection prompts rather than embedding secrets in query logic.
Unexpected duplicates or row counts: Inspect merge keys, source duplicates, and filters before treating the output as complete.

For sensitive information, consider what source data the workbook imports and who can access the workbook or its connections. Power Query does not automatically make a workbook safe for sharing. Follow your organization’s data-handling rules, avoid distributing confidential outputs unnecessarily, and use appropriate access controls for source systems.

Frequently Asked Questions

Is Power Query included in Excel?
Power Query is integrated into modern Excel editions, including Microsoft 365 for Windows. The exact connectors and capabilities can vary by platform, edition, account, and organizational settings.

Does Power Query change my original data?
Usually, it reads the source and loads a transformed result to a worksheet or Data Model. It does not ordinarily edit the original file, though you should still keep appropriate backups of important source data.

Do I need to know coding to use Power Query?
No. The editor provides menus for many common transformations. It also generates steps in the M language, which advanced users can inspect or edit when a task requires more control.

Why does my query not update automatically?
Queries generally need to be refreshed, either manually or through configured refresh options. Automatic refresh may depend on the source, connection, workbook settings, and whether the file is open.

Can Power Query combine multiple Excel files?
Yes. A folder connection can combine files that follow a compatible structure. Keep headers, sheet layouts, and relevant columns consistent, and remove unrelated files from the folder to avoid import errors.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.