To learn Power Query in Excel, start with a table or file, choose Data > From Table/Range or Data > Get Data, make a few transformations in the Power Query Editor, then select Home > Close & Load. Power Query records those steps so you can refresh the result when the source data changes, instead of repeating the cleanup by hand.
What Power Query does
Power Query is Excel’s tool for connecting to data, reshaping it, and loading the result into a worksheet or the Excel Data Model. It is often described as an extract, transform, and load (ETL) tool: you extract information from a source, transform it into a useful structure, and load it where it can be analyzed. Common sources include Excel workbooks, text and CSV files, folders, databases, and online services. The available connectors depend on your Excel edition, setup, and access permissions.
A query is a saved set of instructions, not simply a one-time import. For example, you might tell Excel to open a monthly sales file, remove blank rows, set the date and amount columns to the right types, and keep only records for the current year. When next month’s file follows the same layout, refreshing can repeat those operations. The original source is not changed by transformations in the query.
Power Query is useful when work involves recurring data preparation: consolidating reports, fixing inconsistent formats, filtering unnecessary records, or joining tables. It is less necessary for a one-off task involving a few cells, where ordinary Excel editing may be quicker.
Start with a clean source
For a first exercise, use a small Excel table with headers such as Date, Region, Product, and Sales. Each row should represent one record, and each column should hold one kind of information. Avoid merged cells, title rows above the headers, and totals mixed into the data. Those features are convenient for a presentation but make a source harder to process consistently.
Click inside the data and choose Data > From Table/Range. If the range is not already an Excel table, Excel asks whether the data has headers. Confirm the range and header choice, then select OK. The Power Query Editor opens with a preview. To connect to a separate file instead, use Data > Get Data and choose the appropriate source, such as From File > From Text/CSV or From Workbook. Connector labels can vary slightly between builds.
In the editor, the preview helps you inspect column names, values, and types. A useful habit is to check the first few rows and confirm that headers were recognized correctly before applying changes. If the source is a folder of similarly structured files, the folder connector can combine them; this is most reliable when the files have consistent column layouts.
Make transformations in the editor
Power Query transformations are actions such as removing columns, filtering rows, splitting text, replacing values, or changing a column’s data type. Many are available from the ribbon or by right-clicking a column. Each action is added to the Applied Steps list, usually in the order performed. Selecting an earlier step lets you inspect the query at that point; you can edit or remove a step if a later result is wrong.
For example, a sales export might contain an unused internal ID, blank records, dates stored as text, and a combined “City, State” field. You could remove the ID, filter out blank rows, set the date column to the Date type, and split the location column by its comma delimiter. If a date such as 03/04/2026 could mean March 4 or April 3, check the source’s regional convention before converting it. Where needed, use the data type option that specifies a locale so dates and decimal separators are interpreted correctly.
Power Query is not limited to simple cleanup. The Transform tab includes operations such as pivoting and unpivoting. Unpivoting is especially helpful when a report has one column per month: selecting those month columns and unpivoting them can create a tidy pair of columns, often called Attribute and Value. That structure is easier to filter, chart, and combine with other records.
Combine data with append and merge
Two important operations solve different problems. Append stacks rows from one table under rows from another. Use it for January and February sales files that have the same kinds of columns. Power Query matches columns by their names, not just their positions; if one file uses “Customer” and another uses “Client,” the result may contain separate columns. Rename inconsistent headers before appending, and check for missing values.
Merge matches records across tables using one or more key columns, then lets you expand selected fields from the matching table. For example, merge a sales table with a product list using Product ID to add product category to each sale. Select the matching columns in both previews and choose the appropriate join kind. A left outer join keeps every row from the first table and adds matches from the second, leaving unmatched fields empty. Check that key values have compatible data types and spelling; extra spaces or differently formatted IDs can prevent a match.
After a merge, expand only the fields you need. Also check whether the lookup table has one row per key. Duplicate keys can multiply rows in the merged result, which may inflate totals without an obvious error message. Comparing row counts and a few known records before and after a merge is a practical validation step.
Load the result into Excel
When the query is ready, choose Home > Close & Load to load it using Excel’s default destination. To choose a destination or output type, use Home > Close & Load To. Depending on the query and workbook, options include loading as a table, creating a PivotTable report, or creating a connection without placing the full result on a worksheet. You can also choose to add suitable data to the Data Model for analysis with PivotTables and related tools.
Worksheet tables are convenient because they are visible and easy to reference, but a worksheet can contain at most 1,048,576 rows. If a result is larger, or if you want to build a model from multiple tables, loading to the Data Model may be more appropriate. That option does not remove practical limits: available memory and workbook performance still matter. Choose the destination based on how the data will be used, not just where it is easiest to see.
After loading, Excel typically lists workbook queries in the Queries & Connections pane, available from Data > Queries & Connections. From there, you can inspect a query, edit it, or refresh it. Keep the output table separate from any manually maintained notes; a refresh can replace or resize query output.
Refresh and maintain a query
When source data changes, select a cell in the query output and choose Data > Refresh All to update workbook connections, or right-click an individual query and select Refresh in the Queries & Connections pane. Refresh reruns the saved steps against the source; it does not automatically repair a source whose structure has changed unexpectedly.
For recurring reports, document where the source files live, what columns they need to contain, and who is responsible for replacing or updating them. A query that points to a file on one employee’s computer may fail for a colleague who does not have that same path. Shared storage and stable folder conventions make handoffs more reliable. If the source moves or is renamed, edit the source step in the query rather than rebuilding every transformation.
Some connections require credentials or access to a network location. Excel may ask you to sign in or choose privacy settings when connecting or combining sources. Use only sources you are authorized to access, and do not work around an organization’s security controls. If refresh fails, first check that the file or service is reachable and that your permissions remain valid.
Common problems and how to diagnose them
- Headers appear as data: Check whether the source has an extra title row or whether the first row was promoted incorrectly. In the editor, use the header options to promote or demote headers as appropriate.
- Numbers or dates are wrong: Inspect the column type and regional format. A value that looks like a number may have been imported as text, while a date may have been interpreted using the wrong month-day order.
- Rows disappear after filtering: Review the filter step and the source values. Filters can exclude blanks, spelling variants, or values with leading or trailing spaces that were not anticipated.
- A merge returns empty fields: Check key-column types, spaces, punctuation, and leading zeroes. Postal codes and IDs are often better treated as text than numbers because their formatting can matter.
- Refresh reports a missing file or column: Confirm the path and compare the current source headers with the layout expected by the query. If a source provider changes a column name, dependent steps may need adjustment.
A good troubleshooting method is to inspect the Applied Steps list from top to bottom and find the first step where the preview stops matching expectations. The error often originates there, even if a later step is where Excel displays the warning. Keep a copy of the original source and validate totals, row counts, and representative records after important transformations.
Power Query compared with formulas and macros
Formulas are often best when results need to respond immediately to changes in individual worksheet cells or when the calculation is part of a model. Power Query is better suited to repeatable import and reshaping work across files or tables. A practical workbook may use both: Power Query prepares a clean table, and worksheet formulas or PivotTables analyze it.
VBA macros can automate a wider range of Excel actions, including interface tasks and workbook operations, but they require code and can be harder to inspect or maintain. Power Query provides a visual workflow for many data-cleaning jobs and exposes the transformation sequence. For advanced transformations, it uses the M language in the background; beginners can accomplish a lot without writing M directly. Power Query is not a replacement for a database when many users need controlled, concurrent updates to large operational datasets.
A practical way to learn
Learn by building a small query from a source you understand, then change the source and refresh it. That makes it easier to see which steps are reusable and which assumptions depend on the file layout. A useful progression is:
- Import one Excel table or CSV and confirm its headers and column types.
- Remove an unnecessary column, filter a few rows, and rename a field.
- Split one text column or convert a wide monthly report using unpivot.
- Append two similar files, then try merging a lookup table on a reliable ID.
- Load the output, refresh it after adding a source record, and verify the result.
Do not focus on memorizing every ribbon command. The transferable skills are recognizing a tidy source structure, choosing the right transformation, checking its result, and understanding how a change in the source affects refresh. Once the visual steps feel familiar, the View > Formula Bar and Advanced Editor can help you inspect the M expressions Power Query generates.
Frequently Asked Questions
Is Power Query included in Excel?
Power Query is built into current desktop versions of Excel, including Microsoft 365 for Windows. Connector availability and interface details can differ by platform and build. On Mac, Power Query support is available in Microsoft 365 but does not necessarily include every Windows connector or capability; check the Data tab in your installed version. Excel for the web does not provide the same full authoring experience as desktop Excel.
Do I need to know coding to use Power Query?
No. You can create many queries through the editor’s menus, and Excel records the steps. The M language is useful for some advanced work, but it is not a prerequisite for importing, filtering, renaming, or combining data.
Does Power Query change my original file?
Transformations are applied to the query result, not written back into the source file. The loaded output in the workbook can change when refreshed, so keep manual notes outside the query output table.
Will Power Query update automatically when the source changes?
Not necessarily. A query generally needs to be refreshed, for example with Data > Refresh All. Refresh retrieves the current source and reapplies the saved steps, provided the source is available and still has a compatible structure.
Should I learn Power Query or PivotTables first?
They address different stages of analysis. Learn Power Query when data needs importing, cleaning, or combining; learn PivotTables when you want to summarize and explore prepared data. They work well together, and Power Query can load data for a PivotTable report or the Data Model.
Related
- How to Import CSV to an Existing Excel File
- Import CSV Into an Existing Excel Worksheet
- Import CSV to Excel: Steps, Settings, and Common Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Insert a Dropdown in Google Sheets: A Quick GuideTo insert a dropdown in Google Sheets, select the cell or cells, choose Insert > Dropdown, add your…
- Excel Shortcut to Insert a Row: Windows and MacIn Excel for Microsoft 365 on Windows, select the row where the new row should go and press…
- Excel Find and Replace Wildcards: A Practical GuideYes. In Excel for Microsoft 365 on Windows, press Ctrl+H, enter a pattern in Find what, and use…
- Inserting Images in Excel: Steps, Sizing, and TipsTo insert an image in Excel for Microsoft 365 on Windows, select the worksheet, choose Insert > Pictures…
- How to Share a Group Calendar in New OutlookTo share a group calendar in New Outlook, add the people who need access as members of its…
- Mail Merge in Word, Excel & Outlook: A Step-by-Step GuideTo mail merge in Word with an Excel list and Outlook, prepare a spreadsheet with one recipient per…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.