Excel Power Query Tutorial: Clean and Combine Data

Excel Power Query is a built-in tool for importing, cleaning, reshaping, and combining data, then loading the results into a worksheet or the Data Model. In Excel for Microsoft 365 on Windows, start by choosing Data > Get Data or Data > From Table/Range, make changes in the Power Query Editor, and select Home > Close & Load; afterward, refresh the query to repeat those steps on updated source data.

What Power Query does

Power Query is Excel’s data preparation and connection tool. It can retrieve information from sources such as Excel workbooks, CSV and text files, folders, databases, and some online services. In the editor, you can remove unwanted rows, change data types, split columns, filter records, group values, and combine tables. The result can be loaded to a worksheet table or used as a connection for further analysis.

The key idea is that a query records a sequence of transformations rather than requiring you to repeat the same manual cleanup each time. For example, suppose a weekly sales file contains extra title rows, inconsistent date formats, and columns you do not need. You can configure a query to remove the title rows, assign the correct date type, and keep only the relevant fields. When next week’s file follows the same structure, refresh the query and Excel applies the recorded steps again.

Power Query does not automatically repair every kind of bad data, and it does not guarantee that a source file will keep the same layout. It is best understood as a repeatable preparation workflow: the source remains separate, transformations are documented as steps, and the loaded output is produced from those steps.

Prepare your source data

Before importing, check that the source has a clear structure. A well-formed table usually has one header row, one record per row, and one type of information per column. Avoid merged cells, blank header names, subtotals mixed into the records, and decorative title rows inside the data range. These features can confuse automatic detection or require extra cleanup steps.

For data already in the workbook, click a cell in the range and choose Data > From Table/Range. Excel may ask you to confirm the range and whether it has headers. If the data is not already an Excel table, the dialog can create one. Giving the table a useful name through Table Design > Table Name makes it easier to recognize later. For a separate file, use Data > Get Data and select the appropriate source, such as From File > From Workbook or From Text/CSV.

When importing a workbook, the Navigator lists available sheets and tables. Select the item you need, then choose Transform Data to inspect and edit it before loading. Choosing Load may bring it into Excel immediately, but Transform Data is useful when you want to check headers, types, or unwanted rows first.

Clean and transform data in the editor

The Power Query Editor shows a preview of the data, a ribbon of transformation tools, and usually a Query Settings pane containing Applied Steps. Each action adds a step. Selecting an earlier step lets you inspect the data at that point; you can rename steps to make the workflow easier to understand. The editor’s preview is not necessarily the entire dataset, so a preview that looks correct is not a substitute for checking the source and the final output.

A practical first pass is to confirm headers and types. If the first row contains field names, use Home > Use First Row as Headers when needed. Check the small type symbol beside each column name: dates, whole numbers, decimals, text, and other types behave differently in later operations. If Excel guessed incorrectly, select the column and use Transform > Data Type to set the intended type. For dates with ambiguous day and month order, use Change Type > Using Locale where available, and choose the locale that matches the source.

Other common cleanup actions include:

Remove unnecessary fields by selecting columns and choosing Home > Remove Columns.
Filter out blank records or unwanted categories using the filter menu on a column.
Use Transform > Format to trim or clean text when spaces or nonprinting characters interfere with matching.
Split a combined field, such as a full name or code, with Transform > Split Column.
Replace inconsistent labels, remove duplicate rows, or group records when the analysis calls for it.

Make changes in a deliberate order. For instance, set a numeric column to the correct type before calculating totals or grouping it. If an early step changes the source structure, later steps that refer to a removed or renamed column may fail. Applied Steps helps locate where a query stops working.

Load the result into Excel

When the preview is ready, choose Home > Close & Load to send the result to a worksheet using the default load behavior. To choose where and how it appears, use Home > Close & Load > Close & Load To. Depending on the query and Excel version, the dialog offers choices such as loading a table to a worksheet, creating only a connection, or adding the data to the Data Model. A connection-only query can be useful as an input to another query without displaying an extra worksheet table.

Loading to a worksheet is convenient when people need to inspect or filter the cleaned records. The Data Model is often more appropriate when the dataset is large or when you want to build relationships between tables for a PivotTable. Excel worksheets have a limit of 1,048,576 rows, so a query result larger than that cannot fit in a sheet as a normal table; use a connection or the Data Model when appropriate and available.

Power Query output is generally managed by the query. Avoid typing corrections directly into the loaded result table: a refresh can replace those edits. Fix the source or add a transformation step instead. If you need notes or manual adjustments, keep them in a separate table and combine them deliberately rather than relying on edits inside the query output.

Combine files and tables

Two common operations are append and merge. Append stacks records from tables with compatible columns, placing rows from one table after rows from another. It is useful for monthly or regional files with the same fields. In the editor, use Home > Append Queries or Append Queries as New; the latter creates a separate query rather than changing the current one. Columns are matched by their names, so check spelling and data types before appending.

Merge joins tables by matching values in one or more key columns and adds fields from the second table. For example, a transactions table might contain a product ID, while a product table contains each ID and its category. Merge the tables using the ID fields, then expand the resulting column to include category. Use Home > Merge Queries or Merge Queries as New and select the matching columns in both previews. The join kind controls which matches are kept; a left outer join retains every row from the first table and matching rows from the second.

Before merging, confirm that the key columns have compatible types and consistent values. Extra spaces, different capitalization, leading zeros, or duplicate keys can produce missing or repeated matches. If the lookup table has multiple rows for one key, a merge can increase the number of output rows. Check row counts and unmatched records after expanding the results.

For recurring files stored in one location, the From Folder connector can combine files that share a consistent structure. Use Data > Get Data > From File > From Folder, select the folder, and choose Combine & Transform Data. Review the sample-file steps carefully. Files with different column names, extra header lines, or different formats may not combine as expected. Keep unrelated files out of the folder or filter them from the file list before combining.

Refresh and maintain a query

A query is useful when its output can be refreshed from a dependable source. Choose Data > Refresh All to update workbook connections and queries, or right-click a query in the Queries & Connections pane and select Refresh. The exact refresh time depends on the source and the amount of data. For a workbook query, keep the source file at the expected location and avoid changing its sheet, table, or column names without reviewing the query.

To inspect a query, open Data > Queries & Connections and double-click its name, or use the available edit command from the pane. Query Properties may offer settings such as refreshing data when opening the file or controlling background refresh; options depend on the connection. A workbook opened by someone else may prompt for credentials or access to a source. Refreshing cannot retrieve data that the user is not authorized to access.

For a maintainable workflow, use clear query and table names, remove genuinely unnecessary columns early, and document unusual transformations. Keep a stable copy of source files when practical. If a refresh fails after someone changes the source, compare the current headers and file path with the assumptions recorded in the query rather than rebuilding everything immediately.

Common problems and limitations

A frequent error is a column-not-found message after a source heading was renamed. Edit the source heading back or update the transformation that refers to the old name. A type-conversion error often means the column contains mixed values, such as a number in most rows and the word Pending in one row. Decide how those values should be represented, then clean or transform them before enforcing a single type.

Unexpected blanks after a merge usually point to mismatched keys. Inspect both sides for spaces, inconsistent types, leading zeros, or different spellings. Unexpected extra rows may mean the lookup key is not unique. For a folder combine, confirm that every included file has the same basic layout and that temporary or unrelated files are not being treated as data.

Power Query can also be limited by connector availability, permissions, source size, and the way a particular source exposes data. Some transformations can be sent back to a database for processing, a behavior known as query folding, while others are performed by Excel’s query engine. Whether folding occurs depends on the source and the steps used. You do not need to understand query folding for ordinary cleanup, but it can matter for performance with large database sources.

Excel versions, Mac, and alternatives

Power Query is integrated into Excel for Microsoft 365 on Windows. It is also available in several Windows desktop editions, but the connectors and features can differ by Excel version and license. Excel 2010 and 2013 used a separate Power Query add-in; instructions for those editions do not always match the current ribbon. For current work, check the commands available in your installed version rather than assuming every connector is present.

Excel for Mac has Power Query capabilities, but the available sources and editing features can differ from Windows, and Microsoft continues to update the product. If a Windows tutorial names a connector or command that is absent on your Mac, check Excel’s current Data tab and Microsoft’s version-specific documentation. Excel for the web also has evolving query features and is not a one-for-one replacement for the full Windows editor in every workflow.

For simple, one-off cleanup, ordinary Excel tools such as filters, Text to Columns, or formulas may be quicker. Power Query becomes more attractive when steps must be repeated, multiple files need combining, or the same cleanup should be applied consistently. PivotTables are mainly for summarizing and exploring data, while Power Query prepares it; they often work together, with a query loading clean data for a PivotTable.

Frequently Asked Questions

Is Power Query free in Excel?
Power Query is included in many desktop versions of Excel, including Microsoft 365, so it generally does not require a separate purchase when included in your license. Available features and connectors vary by edition, platform, and subscription. Check the Excel version and license used by your organization.

Does Power Query change the original data?
No. Transformations are applied to the query result, not directly to the source file. When you refresh, Power Query reads the source again and recreates the output according to the saved steps. This separation is one reason it is safer for repeatable cleanup than manually overwriting source records.

How do I update Power Query results?
Use Data > Refresh All, or right-click an individual query in the Queries & Connections pane and choose Refresh. If the source has moved, been renamed, or requires new credentials, resolve that connection issue before refreshing again.

Can Power Query combine multiple Excel files?
Yes. If the files are in one folder and have a compatible structure, use Data > Get Data > From File > From Folder and choose Combine & Transform Data. Review the sample transformation and filter the folder contents so that only intended files are included.

Should I use Power Query or formulas?
Use formulas when you need calculations that update within a worksheet or a small, direct transformation. Use Power Query when importing and cleaning data through repeatable steps, especially across files or sources. They are complementary: a query can prepare a table that formulas or PivotTables analyze.

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.