Excel Power Query is a built-in tool for importing, cleaning, combining, and reshaping data without repeating the same manual edits each time. In Microsoft 365 Excel for Windows, start on the Data tab, choose a source such as Data > Get Data > From File > From Workbook, make changes in the Power Query Editor, then select Home > Close & Load to put the result into Excel.
What Power Query does
Power Query is Excel’s data preparation and connection tool. It takes data from a source, applies a series of recorded steps, and produces a cleaned result. Sources can include Excel workbooks, text and CSV files, folders, databases, and some online services. The exact connectors available depend on your Excel edition, organization settings, and the source.
Imagine that a monthly sales report arrives as a CSV file. It contains several columns you do not need, inconsistent date and number formats, and rows for different regions. You can use Power Query to remove unwanted columns, set the correct data types, filter to the required regions, and load the result as a tidy Excel table. Next month, you can replace or add the source file and refresh the query rather than repeat every cleanup action by hand.
Power Query is not a spreadsheet formula and it does not continuously monitor a file. It prepares data when you create or refresh a query. The source remains separate from the output unless you deliberately edit the source itself.
How a query works
A query has three practical parts: a connection to a data source, a sequence of transformation steps, and a destination for the finished data. In the Power Query Editor, each change—such as removing a column or filtering rows—appears in the Applied Steps list. Power Query records those instructions and runs them in order when the query is loaded or refreshed.
The underlying query language is called M, but beginners can do a great deal through the editor’s buttons and menus without writing M code. Choosing a command creates the corresponding instruction behind the scenes. You can inspect the formula bar or use the Advanced Editor to see or edit M later, but neither is required for ordinary cleanup.
A refresh reruns the recorded steps against the source. That makes the process repeatable, but it also means the source needs to remain accessible and reasonably consistent. If a later report renames a column that a step refers to, the query may fail until you update that step.
Build a first query in Excel
For a first exercise, use a small workbook or CSV file with ordinary columns such as Date, Product, Region, and Sales. Keep an untouched copy of the original data so you can compare the cleaned result and recover if you make an unintended change.
- Connect to the source. For a CSV, choose Data > Get Data > From File > From Text/CSV. For another workbook, choose Data > Get Data > From File > From Workbook. If the data is already in the current workbook, make it an Excel table and choose Data > From Table/Range.
- Choose the data. In the Navigator window, select the relevant sheet, table, or file preview. Choose Transform Data to open the editor and make changes before loading. Depending on the connector, you may also have a direct load option.
- Check the structure. Confirm that the first row contains the intended column names. Use the editor’s data type icons to check dates, whole numbers, decimals, and text. Correct types matter: a date stored as text may not sort or group as a real date.
- Apply a few transformations. Select columns you do not need and use Home > Remove Columns, filter rows with a column’s filter menu, or rename a heading by double-clicking it. Use Transform or Home commands for other changes, such as splitting a column or removing duplicates.
- Load the result. Choose Home > Close & Load to create an output in Excel. To choose where or how it loads, use Home > Close & Load > Close & Load To, where available. Common destinations include a worksheet table or a connection that can be used by other workbook features.
After loading, give the output and the query a clear name, such as CleanSales. In the Queries & Connections pane, you can see the queries in the workbook and, in many cases, their status. Clear names make a workbook easier to maintain when it contains several imports.
Useful transformations and a realistic example
Transformations are the actual cleanup and reshaping operations. For a monthly sales file, a useful sequence might be to promote the first row to headers, remove report notes above the table, delete unused columns, set Date and Sales to the right types, filter out blank product names, and sort by date. These changes are saved as steps rather than applied as one-off edits to the source.
- Filter rows: Keep only the year, region, or status relevant to a report. A filter in Power Query changes the query output, not the original file.
- Split or combine columns: Separate a full name into parts or split a code at a delimiter. Check the preview so that unexpected delimiters do not produce unwanted columns.
- Replace values: Standardize variations such as abbreviated region names. Inspect the source values first so a broad replacement does not change unrelated text.
- Remove duplicates: Use this only when repeated rows are truly duplicates for your purpose. Two sales with the same customer and date are not necessarily duplicate records.
- Group rows: Group by fields such as month or region and calculate summaries when an aggregated output is more useful than individual records.
Order matters. For example, filtering out blank rows before changing a column’s type can make the preview easier to interpret, while removing a column too early can prevent a later step from using it. If a step shows an error, select earlier steps one at a time to identify where the result changes unexpectedly.
Combine files and tables
Power Query can combine data in two different ways that beginners often confuse. Append stacks rows from tables with matching or similar columns. It is useful when January, February, and March files contain the same kind of sales records. Merge joins tables using one or more matching columns, much like looking up information from a second table. For instance, you might merge a sales table with a product list to add each product’s category.
For files stored together in a folder, choose Data > Get Data > From File > From Folder, select the folder, and use the combine option in the preview workflow. This is convenient when every file follows the same layout. Before relying on it, check that the files have compatible columns, headers, and formats; a stray temporary file or a differently structured report can disrupt the result. Keep new files in the expected folder and refresh the query to include them.
When merging, choose the correct key columns and verify that their values have compatible types and formats. A product ID stored as text in one table and as a number in another may not match as expected. Also check the join kind: a left outer join, for example, keeps every row from the first table and adds matching information from the second where available.
Refresh, loading, and workbook design
Once a query is loaded, use Data > Refresh All to update workbook connections, or right-click an individual query or output and choose Refresh where that option is shown. Refresh uses the source and saved steps to rebuild the output. It does not necessarily mean the source changed: refreshing can simply run the same process again.
For a small result that needs to be visible or edited in Excel, a worksheet table is often the clearest destination. A query can also be set up as a connection without placing its rows on a sheet, which is useful when another query or workbook feature will use it. Loading to the Data Model is an option for analysis with tools such as PivotTables, but it is not necessary for a beginner’s first import.
Plan the workbook so that query outputs are not mixed with hand-entered notes. A refresh may replace or resize the output table, so keep manual commentary in a separate area or sheet. If other formulas refer to the output, use the table’s column names where practical and check those formulas after changing the query’s columns.
Power Query compared with formulas and PivotTables
Power Query, formulas, and PivotTables solve related but different problems. Use Power Query to import and prepare source data, especially when the same cleanup must be repeated. Use formulas when you need calculations in cells that respond directly to workbook inputs or need row-by-row logic. Use a PivotTable to summarize and explore a prepared dataset by categories such as product, month, or region.
They can work together. A common workflow is to clean and combine several source files with Power Query, load the result as a table or to the Data Model, and then use a PivotTable to summarize it. Power Query does not replace every formula, nor does it make a PivotTable unnecessary. Choosing the tool that fits each stage usually makes a workbook easier to update and explain.
Common problems and safe habits
A query error often points to a change in the source rather than a problem with Excel itself. Check whether a file was moved or renamed, a column heading changed, a date uses a different format, or an account no longer has permission to access the source. The Applied Steps list is a useful diagnostic: select steps from top to bottom and look for the first one that produces an error or unexpected preview.
- Wrong data types: Review the type icons and correct columns before loading. For ambiguous dates, confirm the intended day-month order rather than assuming Excel interpreted them correctly.
- Missing rows or unexpected blanks: Review filters and any steps that remove errors, blank values, or duplicates. A cleanup choice can exclude valid records.
- Refresh prompts or access failures: Confirm that the source path is available and that the required sign-in or permissions are valid. Do not share credentials in a workbook or enter them into an untrusted prompt.
- Privacy and external data: Connect only to sources you are authorized to use. When Excel asks about privacy levels for sources, choose settings that reflect the data and your organization’s rules; these settings can affect how sources are combined.
Power Query reads and transforms data according to the steps and connectors you select. Treat unfamiliar workbooks and external sources cautiously, especially if they request credentials or contain connections you did not expect. Review the Queries & Connections pane and source settings before refreshing a workbook received from someone else.
Excel versions and alternatives
This guide describes Microsoft 365 Excel for Windows. Excel for Mac includes Power Query capabilities, but available connectors, commands, and editing options can differ by version and update channel. Excel for the web also has a more limited or changing set of query authoring and refresh capabilities than the Windows desktop application. If a command in this guide is missing, check Microsoft’s current support information for your specific platform and build rather than assuming the query itself is broken.
If Power Query is unavailable or unsuitable, alternatives include importing a small file manually, using formulas to clean a limited dataset, or using a database or another data-preparation tool for larger or more complex workflows. For recurring imports, however, a manual copy-and-paste process is usually harder to audit and repeat. Power Query is a practical middle ground: it provides a visual workflow while preserving a reusable sequence of steps.
Frequently Asked Questions
Is Power Query free in Excel?
Power Query is included in supported editions of Excel, including Microsoft 365 desktop Excel, but the available connectors and features can depend on the license, platform, and organization settings. Check the features available in your installed Excel version.
Do I need to know coding to use Power Query?
No. You can create and manage many queries through the ribbon and menus in the Power Query Editor. M is the language behind the steps, and learning some M can help with advanced customizations, but it is not a prerequisite for basic imports and cleanup.
Does Power Query change my original file?
Normally, its transformations create a query result rather than editing the source file. The result is loaded into the workbook or made available as a connection. Keep the source unchanged if you need an original record to compare against.
How do I update Power Query data?
Make sure the source file or connection is available, then use Data > Refresh All or refresh the individual query. The query reruns its saved steps. If the source structure has changed, you may need to edit the query before refresh succeeds.
Can Power Query combine multiple Excel files?
Yes. The From Folder connector can combine files that share a compatible structure. Check headers, column types, and sample files first, and remove unrelated files from the folder so they are not included unintentionally.
Related
- Project Tracker Spreadsheet Software Explained
- How to Use Slide Master View in PowerPoint Effectively
- Project Tracker Spreadsheet: Free Options and Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Not Responding? Causes and FixesIf Excel says Not Responding, give it a short time to finish before closing it; the message can…
- Google Sheets 2D SUMIF: Sum Values by Row and ColumnGoogle Sheets has no built-in 2D SUMIF function, but you can sum a table by matching a row…
- Compare Documents in Word: See Every ChangeTo compare two versions of a document in Microsoft Word, open Word and choose Review > Compare >…
- How to Insert a Checkbox in Word With a TickTo put a tick in a Word checkbox, insert a checked-box symbol for a fixed mark, or add…
- Customer Contact Details List in Excel: Build a Useful SheetTo make a customer contact details list in Excel, create columns for each piece of information you need—such…
- How to Group Rows in Excel Under a Heading: Step-by-StepTo group rows under a heading in Excel, leave the heading row outside the selection, select the detail…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.