Excel Power Query: How to Extract and Refresh Data

How-to basics · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, select Data > Get Data, choose a source such as a workbook, CSV file, folder, or web page, then use Power Query Editor to select and transform the data before loading it into Excel. Afterward, use Data > Refresh All to retrieve updated source data and apply the same steps again.

What Power Query Does When It Extracts Data

Power Query is Excel’s tool for connecting to data, shaping it, and loading the result into a worksheet or the Data Model. It is often described as an extract, transform, and load tool: it extracts information from a source, lets you transform that information, and loads the result where you can analyze it. The source might be a file on your computer, a database, or another supported service.

A key distinction is that Power Query generally does not edit the source file. Instead, it records the connection and the sequence of transformation steps, then produces a separate output. For example, you can import a sales CSV, remove blank rows, keep only the current year, and load the cleaned records as an Excel table. The original CSV stays unchanged.

This repeatable process is useful when the same kind of cleanup is needed regularly. Rather than manually copying and adjusting data each month, you can refresh the query when a new file or updated source is available. Power Query repeats its recorded steps, although a refresh can still require attention if a source moves, its structure changes, or access is denied.

Common Sources You Can Connect To

On Windows, the Data tab’s Get Data options include connectors for many common sources. The exact connectors shown can depend on the Excel build, installed components, and your organization’s settings. Common choices include:

  • Excel workbooks: Select Data > Get Data > From File > From Excel Workbook to choose a workbook and then select a sheet or table.
  • Text and CSV files: Select Data > Get Data > From File > From Text/CSV. The preview lets you check how Excel has interpreted the file before importing it.
  • Folders: Select Data > Get Data > From File > From Folder to work with a collection of similarly structured files.
  • Tables or ranges in the current workbook: Select a cell in the data and choose Data > From Table/Range. Excel turns a range into a table if needed.
  • Databases and online sources: Use the corresponding options under Get Data when the connector is available and you have permission to access the source.

For data already in a worksheet, converting the range to a well-formed table first can make the source easier to manage. Give columns meaningful headers, avoid merged cells in the data area, and keep each column to one kind of information where practical. A tidy source is easier to inspect and less likely to produce confusing results.

How to Extract Data Step by Step

For a typical file import in Excel for Microsoft 365 on Windows, use this sequence:

  1. Open the workbook where you want the extracted data to appear. On the Data tab, select Get Data and choose the connector that matches the source, such as From File > From Text/CSV.
  2. Browse to the file, select it, and review the preview. For a CSV, check that columns are separated correctly and that dates, decimal values, and special characters look as expected.
  3. Select Transform Data to open Power Query Editor. If the preview is already correct and no changes are needed, you can choose a load option instead, but opening the editor gives you control over the data before it reaches the worksheet.
  4. In the editor, select the columns and rows you need and make any required changes. The steps appear in the Applied Steps pane, where you can select a step to inspect it or remove it.
  5. Choose Home > Close & Load to load the result using the default option, or use Home > Close & Load To to choose a destination and loading method.

For an existing worksheet range, select a cell inside the data and choose Data > From Table/Range. Confirm whether the first row contains headers when Excel asks. This opens the range in the editor as a query source; it does not mean the query will automatically include unrelated cells outside the table.

On Mac, Power Query is available in Microsoft 365, but connector availability and authoring features can differ by Excel version and build. The Windows ribbon paths above may not match exactly. Excel for the web also has different Power Query capabilities from the Windows desktop app, so use the desktop version when a connector or editing command is unavailable in your interface.

Choose and Transform the Data You Need

Power Query Editor provides a preview of the source and commands for changing its shape. Common operations include removing columns, filtering rows, sorting, splitting a column, changing data types, replacing values, and removing duplicates. These operations create steps in the query rather than changing the original source file.

Consider a CSV export with transaction date, region, product, quantity, and sales amount. If a report needs only the current year and three columns, you could filter the date, retain region, product, and sales amount, and set the date and amount columns to appropriate data types. The loaded table is then a smaller, more focused result than the original export.

Check data types instead of assuming the preview has identified them correctly. A date imported as text may sort alphabetically, and a product code such as 00127 may lose its leading zeros if treated as a number. If the code is an identifier rather than a value for calculation, use a text type. For CSVs with unusual separators or character encoding, verify the import settings in the preview before building transformations on top of them.

Combine Files or Queries

Power Query can bring together records from multiple sources. Two operations that sound similar solve different problems:

  • Append stacks rows from one query under rows from another. Use it for monthly exports with matching columns, such as January and February sales records.
  • Merge matches rows between queries using one or more related columns. For example, merge an orders query with a product list using a product ID to add product descriptions.

For a folder of monthly files, the From Folder connector can help combine files that share a consistent layout. After choosing the folder, review the file list and filter out items that should not be included, such as temporary files or an unrelated export. The combine process uses an example file to define how the files are interpreted. If one month has different headers or column formats, the combined result may contain missing values or errors that need investigation.

When merging, check that the matching columns use compatible data types and represent the same kind of identifier. A customer number stored as text in one query and as a number in another may fail to match as expected. Also consider whether the key is unique: if the lookup query has several rows for one key, a merge can produce more rows than the original orders query.

Load the Result and Understand Its Limits

Use Home > Close & Load To in Power Query Editor when you want to choose where the result goes. Depending on the query and Excel version, you can load it as a table in a worksheet, create a connection without displaying the rows in a sheet, or add it to the Data Model for analysis with tools such as PivotTables. A connection-only query can also serve as an intermediate step for another query.

A worksheet has a limit of 1,048,576 rows, so a result larger than that cannot fit in a single sheet. For larger datasets, loading to the Data Model may be a better option, but it has its own capacity and memory considerations. The best destination depends on how you intend to use the data: a visible table is convenient for inspection, while the Data Model is designed for analysis without placing every row on a worksheet.

Loading is not the same as creating a live link that updates every second. A query returns data when it is first loaded and when it is refreshed. Refresh behavior and credentials depend on the source and workbook settings, so check the query’s connection properties if you need a particular refresh schedule.

Refresh a Query and Keep It Reliable

To retrieve changes from the source, select Data > Refresh All. To refresh one query, select a cell in its output table and use the refresh command available on the Data tab. Excel reruns the query’s steps against the source; it does not simply append the latest records unless the query and source have been designed to do that.

For example, if a query imports a file named April.csv, replacing that file’s contents with a newer export may work when its location, name, and structure remain the same. If the new export is saved under a different name or folder, the query may still point to the old path and fail. A folder-based query can be more suitable when new files are added regularly, provided the files have a consistent structure and the query filters the intended files.

Refresh can also be controlled through query or connection properties, including options for refreshing when the workbook opens in applicable cases. These settings vary by connection type. If a workbook is shared, remember that other users may need access to the source and may be prompted to provide credentials. A refresh that works for one user does not guarantee that every recipient can access the same file, database, or online account.

Common Problems and Practical Fixes

  • The source cannot be found: Confirm the file path, file name, and permissions. If the file moved, edit the source step or reconnect to the correct location.
  • Columns are missing or renamed: Check whether the source layout changed. Steps that refer to an old column name can fail; update the source or revise the affected step in the editor.
  • Dates or numbers look wrong: Review the data type and import locale. Text values that look like dates or decimals may need an explicit type conversion using the appropriate regional interpretation.
  • A folder combine gives unexpected rows: Inspect the file list and filters. Exclude unrelated files and check that each included file has compatible headers and columns.
  • Refresh asks for access: Sign in or update permissions for the connection, and confirm that the account has access to the source. Avoid sharing credentials in the workbook or in an unsecured location.
  • Duplicates appear after a merge: Check whether the matching key occurs more than once in the lookup query. Decide whether to remove duplicates, aggregate records, or use a different key before merging.

When a query fails, open it through Data > Queries & Connections, then inspect the error and the Applied Steps list. Select earlier steps to find where the preview first becomes incorrect. That is often more useful than deleting the entire query and starting over, especially when only one column name or type has changed.

Power Query Compared With Other Excel Methods

Manual copy and paste is quick for a one-time, small task, but it does not preserve a repeatable set of cleanup steps. Excel formulas can be better when the result needs to respond immediately to edits within the workbook or when only a small calculation is required. PivotTables summarize data, but they are not a general-purpose replacement for importing and reshaping a source.

Power Query is a strong choice when the same import and cleanup must be repeated, when several files need combining, or when you want a traceable sequence of transformations. It is not necessarily the right tool for every job: if the source is a live operational system, a database administrator or approved reporting connection may be needed; if the data is already well structured and only needs a quick calculation, a worksheet formula may be simpler.

Frequently Asked Questions

How do I extract data from another Excel workbook?
Select Data > Get Data > From File > From Excel Workbook, choose the file, select the sheet or table in the Navigator, and choose Transform Data or Load. Use Transform Data if you need to filter or reshape the imported records first.

Can Power Query extract only certain rows or columns?
Yes. In Power Query Editor, select the columns to keep or remove and apply filters to limit rows. The query stores those choices as steps, so a later refresh applies the same selection to the updated source.

Does Power Query change the original data?
Usually, no. It reads from the source and loads a separate result into Excel. Editing the loaded output table does not normally change the source file, and a later refresh can replace the query results with data returned from the source.

Can Power Query automatically refresh data?
It can refresh manually with Data > Refresh All, and some connections provide settings for refreshing when the workbook opens or at intervals. The available settings depend on the connection type, and refresh requires the source to be reachable and authorized.

Why does my query return an error after the source changes?
A changed file path, renamed column, altered data type, or different file layout can break a recorded step. Open the query in Power Query Editor, identify the first step that fails, and adjust it to match the new source structure.

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.