Excel Power Query: Get Data, Clean It, and Load It Easily

How-to basics · Updated November 21, 2026

To get data with Power Query in Excel for Microsoft 365 on Windows, open the Data tab, choose Get Data, select a source such as From File > From Workbook or From Text/CSV, then choose Transform Data or Load. Power Query imports data into a repeatable process that can clean and reshape it before placing the result in a worksheet or data model.

What Power Query does in Excel

Power Query is Excel’s data import and transformation tool, also called Get & Transform Data. It connects to a source, records the steps you apply to the data, and runs those steps again when you refresh the query. Typical tasks include removing unwanted rows, changing column types, splitting a column, combining files, and joining tables from different sources.

The important distinction is that Power Query does not usually alter the original source file. It creates a query that describes how to retrieve and shape the data. The finished output is loaded separately into Excel. If the source changes, refresh the query to retrieve the latest data and repeat its transformation steps. This makes Power Query useful for recurring work that would otherwise involve repeating the same manual edits.

It is not a replacement for every Excel feature. Formulas are often better for calculations that need to update cell by cell, while PivotTables are designed to summarize data interactively. Power Query is especially useful for preparing and combining source data before analysis.

Get data from a file or another source

In Excel for Microsoft 365 on Windows, start on the Data tab. Select Get Data and choose a connector. Depending on the source, Excel may show a preview or open the Power Query Editor before loading anything. Common routes include:

  • Excel workbook: Data > Get Data > From File > From Excel Workbook. Browse to the workbook, select it, then choose a sheet or table in Navigator.
  • CSV or text file: Data > Get Data > From File > From Text/CSV. Check the delimiter and preview, then select Transform Data to edit the query or Load to bring it into Excel.
  • Folder: Data > Get Data > From File > From Folder. Choose the folder, then use the combine option when its files have a consistent structure.
  • Other connections: The Get Data menu includes connectors for supported databases, online services, and other file types. Available connectors depend on the Excel version, operating system, and access permissions.

For a simple one-time import, Excel’s Data > From Text/CSV command can also be a quick route. Choosing to transform the preview opens the same query-editing workflow. If you need a source that is not listed, check whether Excel provides an appropriate connector or whether the source can be exported to a supported file format.

Choose between loading and transforming

After selecting a source, choose Transform Data when you need to inspect or change its structure. Choose Load when the preview already has the columns and values you need. Loading immediately is convenient, but it does not prevent you from editing the query later: open the query from Data > Queries & Connections, then edit it.

In the Power Query Editor, the preview lets you select columns and apply transformations. The Applied Steps list records the operations in order. Selecting a step shows the data at that point; deleting or changing a step can affect later steps, so check the preview after making a change. The editor may show only a preview of the source rather than every row, but the query applies its steps to the data when it runs.

When finished, select Home > Close & Load to use the default load behavior. Use the arrow beside that command and choose Close & Load To when you want to specify where the results go. Depending on the query and workbook, you can load a table to a worksheet, create a connection, or add data to the Data Model.

Clean and reshape data in the editor

Power Query transformations are useful because they are explicit, ordered steps rather than a sequence of edits to the source sheet. For example, a monthly sales export might include report titles above the actual column headers, blank lines, and amounts stored as text. In the editor, you could remove the top rows, promote the correct row to headers, filter out blank records, and set the amount column to a numeric type.

Common transformations include:

  • Removing, renaming, reordering, or selecting columns.
  • Filtering rows, removing duplicates, and keeping or removing top rows.
  • Splitting a column by a delimiter, such as separating a full name at a comma.
  • Replacing values, trimming text, and changing data types such as date, whole number, or decimal number.
  • Unpivoting columns to turn a wide table into a row-based structure that is often easier to analyze.
  • Appending tables with similar columns or merging tables using matching key columns.

For example, suppose a report has one column for each month and one row per product. Unpivoting the month columns can produce three fields—Product, Month, and Sales—instead of many separate month columns. That long format is often simpler to filter, chart, or summarize with a PivotTable. Check the resulting values and data types, especially dates and numbers that may be interpreted differently because of regional settings.

Combine files from a folder

A folder query can help when you receive a new file each week or month and want one combined table. Point Power Query to the folder, review the file list, and choose the combine option. Excel uses an example file to create a set of transformation steps, then applies those steps to the other files. On refresh, it can include new files placed in the folder.

This works best when the files have a consistent layout: matching column names, compatible data types, and the same general structure. Files with different headers, extra title rows, or a different sheet name can cause missing columns or errors. Before combining, remove unrelated files from the folder or filter the file list by name or extension. It is also sensible to keep the output workbook outside the input folder; otherwise, a saved output file might be picked up as another source file in some workflows.

After combining, inspect records from more than one source file. A successful preview does not guarantee that every file had the expected layout. If one file is an exception, correct its structure or adjust the query to handle the variation deliberately.

Refresh a query and understand its limits

After loading a query, refresh it from Data > Refresh All to update the workbook’s connected data. You can also right-click a query table and choose Refresh, or use the query’s options in the Queries & Connections pane. Refresh reruns the connection and transformation steps; it does not repeat manual edits made directly to the loaded output table as part of the query.

That last point matters. If you type a correction into a cell inside the loaded results, a refresh can replace the output with newly returned query results. Put lasting corrections into the source or add them as a transformation in the query. If a value is intended as an analyst-entered override, keep it in a separate table and combine it deliberately rather than relying on hand edits in the output.

Refresh can fail if a file was moved, a network location is unavailable, credentials expired, or the source changed its structure. Some connections also require access to a company network or permission from an administrator. Check the error message and confirm the source path and credentials before changing the transformation steps. For sensitive sources, follow your organization’s rules for data access and sharing; avoid putting credentials in query text or workbook notes.

Power Query, formulas, and PivotTables

These tools solve related but different problems. Power Query prepares data by importing, cleaning, and combining it. Excel formulas calculate results in cells and can respond immediately to changes in referenced values. PivotTables summarize data for interactive analysis. They can be used together: import and standardize several files with Power Query, load the prepared table, and build a PivotTable from it.

For a small list that needs a one-off correction, editing the worksheet may be faster than creating a query. If the same cleanup must be repeated every reporting period, a query can save time and make the procedure visible. If users need a formula result to recalculate as they edit inputs, a worksheet formula is usually more appropriate. Choose based on whether the main need is repeatable preparation, live cell calculation, or summary analysis.

Power Query is also distinct from a PivotTable’s refresh. A PivotTable summarizes its underlying data; it does not normally fetch and clean a set of source files by itself. Refresh the query first when its source has changed, then refresh the PivotTable if needed so the summary reflects the updated table.

Windows, Mac, and Excel for the web

The instructions above use Excel for Microsoft 365 on Windows, where the Get Data ribbon and Power Query Editor offer the broadest commonly used workflow. Excel for Mac includes Power Query capabilities, but supported connectors and authoring features can differ by release and may not match Windows. Look under Data > Get Data to see the options available in your installed Mac version; a missing connector may require another route, such as exporting the source to CSV.

Excel for the web also has Power Query features, but available sources and editing capabilities can differ from the desktop application and may change over time. If you cannot create or edit a particular query in the browser, open the workbook in the desktop app that supports its connector. For shared workbooks, confirm that other collaborators can access the source and refresh the query under their own permissions.

Frequently Asked Questions

Where is Get Data in Excel?
In Excel for Microsoft 365 on Windows, open the Data tab and select Get Data. Choose a source from the menus, such as From File > From Workbook. The exact choices can vary by platform and Excel release.

Can Power Query import data from another Excel workbook?
Yes. Use Data > Get Data > From File > From Excel Workbook, select the file, then choose the sheet or table in Navigator. Select Transform Data if you need to clean it before loading.

How do I update Power Query results?
Use Data > Refresh All, or refresh an individual query from its table or the Queries & Connections pane. The source must still be reachable, and you need permission to access it.

Does Power Query change my original data?
Typically, no. It reads the source and applies transformations to the query result; it does not edit the original workbook or file. The output loaded into Excel is separate, though refreshing can replace changes typed directly into that output.

Can I use Power Query without loading a table to a worksheet?
Yes. Choose Close & Load To and select a connection-only option when available, or load the query to the Data Model for analysis. The choices shown depend on the query and Excel version.

When Power Query is worth using

Power Query is a strong choice when data arrives repeatedly, comes from multiple files, or needs the same cleanup each time. Start with a small representative source, make the transformation steps clear, and verify the loaded result against the original. Once the query is set up, a refresh can repeat that preparation instead of requiring another round of manual copying and editing.

Related

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.