Excel Power Query Tutorial for Beginners

To use Power Query in Excel for Microsoft 365 on Windows, select your data source from the Data tab, clean or reshape it in the Power Query Editor, then choose Home > Close & Load. The result is a repeatable process: when the source data changes, refresh the query instead of repeating each cleanup step by hand.

What Power Query does

Power Query is Excel’s data import and transformation tool. It can connect to files, folders, databases, and other supported sources; perform operations such as filtering, splitting, and combining data; and load the outcome into a worksheet table or, in some cases, the Excel Data Model. The original source is not changed by ordinary query transformations. Instead, Power Query records instructions and applies them when it retrieves the data.

Those instructions appear in the Applied Steps pane in the editor. For example, a query might record that it promoted the first row to headers, removed blank rows, changed a column to a date type, and filtered out canceled orders. When you refresh, Excel runs those steps against the current source. This makes Power Query useful for recurring reports and data that arrives in the same general structure each week or month.

Power Query is not the same as a worksheet formula. It is designed mainly to import and prepare data, rather than calculate a live result in every cell. Excel stores the steps in the query; the underlying expression language is called M, but beginners can do a great deal through the editor’s menus without writing M code.

Prepare a source table

Before importing, check that the source has a clear, consistent structure. A tidy dataset generally has one header row, one record per row, and one kind of information per column. Avoid merged cells, blank rows inside the data, repeated header rows, and totals mixed into the records. Those features can make automated cleanup harder.

For a worksheet range, select a cell in the data and press Ctrl+T to turn it into an Excel table. Confirm that My table has headers is selected if the first row contains column names. Give the table a useful name on the Table Design tab, such as SalesData. A named table can expand when you add rows, which is helpful when a query reads new entries from it.

On Mac, the equivalent shortcut for creating a table is usually Command+T. Ribbon layouts and available connectors can vary by Excel version, so use the Table or Data tabs if a shortcut behaves differently. For a CSV or other external file, you do not need to convert it to an Excel table first.

Beginner tutorial: import and clean data

Suppose you receive a sales file with columns for Order ID, Date, Region, Product, Units, and Status. It contains a few blank rows, inconsistent date formatting, and canceled orders. You want a clean table for a report, and you expect to receive a revised file each week. The following steps create a refreshable query.

For a CSV file, choose Data > Get Data > From File > From Text/CSV. Browse to the file and select it. Excel displays a preview. Choose Transform Data to open the Power Query Editor; choose Load only if the preview already has exactly the structure you need.
For data already in an Excel table, click inside the table and choose Data > From Table/Range. Excel opens the same editor. If prompted, confirm that the first row contains headers.
Inspect the column names and types. Power Query often detects types automatically, but check them rather than assuming they are right. Select the Date column and choose the date type from its type icon or the Transform tab. Set Units to a whole number if it contains counts. A type mismatch can cause errors or unexpected sorting.
Remove empty records by opening the filter menu on a column that should always contain a value, such as Order ID, and excluding blank entries. To remove entirely blank rows, use Home > Remove Rows > Remove Blank Rows when that command is available in the editor. Check that the operation does not discard legitimate records.
Filter Status to exclude canceled orders if the report should contain completed or active sales only. Select the filter arrow in the Status column and clear the canceled value. Power Query adds a step; it does not delete canceled orders from the source file.
Rename unclear column headings by double-clicking a header, or right-click a column for transformation options. Remove columns the report will never use by selecting them and choosing Home > Remove Columns. Keeping only necessary fields can make a result easier to inspect.
Review the preview and the Applied Steps list. If a step is wrong, select it and use the editor’s controls to change or remove it. Steps can depend on earlier steps, so removing one may affect later operations.
Choose Home > Close & Load to put the result into a new worksheet. To choose a destination or load as a connection instead, use the Close & Load dropdown and select Close & Load To. Available options can depend on the query and workbook.

The loaded table is a result of the query, not a second editable copy of the source. You can format the result for presentation, but a refresh can replace its data. Make lasting corrections in the original source or add the correction as a query step, rather than relying on manually changing a loaded value.

Transformations worth learning first

Most beginner cleanup tasks are available from the Home and Transform tabs or the menus on each column. These operations are applied in sequence, so their order matters. For instance, filtering out unwanted records before grouping can produce a different summary from grouping everything first.

Filter and sort: Keep only the records or date ranges a report needs, or sort a column for easier inspection. A filter step changes the query output, not the source.
Split or combine columns: Split a full name or a code at a delimiter, such as a comma or hyphen. Combine columns when fields need to become one label. Check the preview for values that do not follow the usual pattern.
Replace values and trim text: Standardize labels such as North, NORTH, and N. Use text transformations such as Trim to remove stray spaces that can make apparently identical labels group separately.
Group rows: Group by fields such as Region and summarize Units or Sales. This is useful for turning transaction-level records into a compact report, though it removes the individual detail from that query’s output.
Unpivot columns: Convert a wide layout, such as separate columns for January, February, and March, into rows with a month field and a value field. That normalized structure is often easier to filter, chart, and combine with other data.

Combine files and tables

Power Query can combine data in two different ways that are easy to confuse. Append stacks rows from tables with compatible columns. Use it when January and February exports contain the same fields and you want one longer transaction list. Column names and data types should be consistent; otherwise, the result may contain extra columns or null values.

Merge matches rows using one or more key columns, much like a database join. For example, merge an Orders table with a Products table using Product ID to add product descriptions. Choose the matching columns in both tables and select the appropriate join kind. A left outer join, commonly useful for retaining every row from the first table, keeps all its records and brings in matching values from the second table. Duplicate keys in the second table can produce multiple output rows, so check that the key is unique when that is what the task requires.

For recurring monthly files, Excel can connect to a folder through Data > Get Data > From File > From Folder. The combine workflow is most reliable when files share a consistent layout and are stored together. Remove unrelated files from the folder or filter them out in the query, and test the process when a new file is added.

Refresh, load, and manage queries

After the source changes, select a cell in the loaded query table and choose Data > Refresh, or use Data > Refresh All to update connections and queries in the workbook. Refresh runs the saved steps again; it does not automatically repair a source whose columns, file name, or location have changed.

To see or edit a query, open Data > Queries & Connections and use the available query options, such as Edit. In the editor, you can adjust the steps and then close and load again. If a query loads to a worksheet, the output is convenient to inspect. If it is used only as an intermediate query for another query, loading it as a connection can avoid creating an unnecessary worksheet table.

Consider refresh settings when a workbook is shared or used for a scheduled routine. Excel’s connection properties may offer options such as refreshing data when the file opens, depending on the connection. A refresh still needs access to the source and, where required, valid credentials. A colleague who receives the workbook may not have the same file path, permissions, or sign-in access.

Common problems and practical checks

Dates appear as errors or the wrong day and month: The source may use a different regional date convention. In the editor, use the type conversion options, including Change Type with Locale where available, and choose the locale that matches the source data.
A refresh says a column was not found: The source may have renamed or removed a header. Either restore a stable source header or edit the query steps that refer to the old name. Avoid changing field names casually in a recurring source.
New rows do not appear: Confirm that the query points to the right table, file, or folder, then refresh. If a query reads a fixed worksheet range rather than an expanding table, rows added outside that range may not be included.
Numbers are treated as text: Look for symbols, spaces, or regional decimal separators. Set the appropriate type after addressing the source formatting, and verify the preview rather than relying only on the column icon.
Rows multiply after a merge: The lookup table may have more than one row for a key. Remove or resolve duplicate keys if the intended relationship is one-to-one, or account for the one-to-many result if it is expected.

When results look wrong, compare a few records in the source and output, then inspect Applied Steps from top to bottom. A query is transparent enough to diagnose in stages: each step has a preview, and a later step may be the first place where a value disappears or changes.

Power Query versus formulas and PivotTables

Power Query is a strong choice when the same import and cleanup must be repeated, especially across multiple files or data sources. A formula is often better for a calculation that should update immediately in worksheet cells or for a small, visible calculation beside the data. A PivotTable is designed to summarize and explore a prepared dataset interactively. These tools can work together: use Power Query to clean and combine source data, load it to Excel, then build a PivotTable for analysis.

Power Query is not a replacement for every spreadsheet task. It is less convenient when a person needs to edit individual output cells as if they were source records, or when a calculation must respond directly to each cell edit without refreshing. It also does not remove the need to validate assumptions: automated steps can repeat a mistake just as reliably as they repeat a correct transformation.

Version and platform notes

This walkthrough uses Excel for Microsoft 365 on Windows, where the Data tab includes the Get Data and query tools described above. Excel for Mac has Power Query features, but connector availability and parts of the authoring experience can differ by Excel version and source. If a named connector or menu is missing, check the current Data tab and Microsoft’s support information for that version.

Excel for the web can work with some workbook queries and supports certain refresh scenarios, but its capabilities are not identical to the full Windows desktop editor. For building or changing a complex query, use a supported desktop version. Refresh also depends on the source type, account permissions, and connection setup, so do not assume every desktop connection can be refreshed in a browser.

Frequently Asked Questions

Is Power Query free in Excel?
Power Query is built into supported desktop versions of Excel, including Microsoft 365, rather than being a separate add-in you normally need to buy. The exact tools and connectors depend on the Excel edition, platform, and data source.

Does Power Query change my original data?
Ordinary query steps create a transformed result and leave the source unchanged. If you edit the original file or table, that source can of course change; a later refresh then reads its current contents.

Do I need to learn M to use Power Query?
No. The editor creates M instructions for many common actions, such as filtering, changing types, and removing columns. Learning M can help with custom transformations, but it is not necessary for a basic workflow.

Can Power Query update automatically when new data arrives?
A query can be refreshed to retrieve changed data, and some connections offer refresh-on-open settings. New data is included only if it falls within the query’s source, such as an expanding Excel table or the folder being combined.

Should I use Append or Merge?
Use Append to place similar tables one beneath another. Use Merge to match records across tables using shared key columns, such as matching Product ID in an order list to Product ID in a product lookup.

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.