How to Use Power Query in Excel
Use Power Query in Excel to import, clean, reshape, and refresh messy data without repeating manual spreadsheet cleanup every week.
Power Query is Excel's built-in tool for importing and transforming data before it lands in a worksheet. It is useful when the same export needs the same cleanup over and over: remove columns, rename fields, split text, change types, filter rows, and combine files.
Use Power Query when manual cleanup has become a recurring process.
Start from a table or file
Step 1. Put the source data in an Excel Table, or choose Data -> Get Data if the source is a CSV, workbook, folder, or database.
Step 2. Open the data in Power Query Editor.
Step 3. Apply cleanup steps such as removing columns, changing data types, trimming text, splitting fields, or filtering rows.
Step 4. Choose Close & Load to send the cleaned result back to Excel.
Each transformation becomes a step that can be refreshed later.
Power Query is also called Get & Transform in Excel. Microsoft documents it across Excel for Windows, Mac, and the web, but available connectors and advanced capabilities vary by Excel version and platform.
Example: clean a monthly expense export
Suppose a bank export has transaction date, description, amount, pending flag, and extra columns you do not need.
In Power Query, you might:
- Remove unused columns.
- Change transaction date to a Date type.
- Change amount to a Decimal Number type.
- Filter out pending transactions.
- Trim spaces from descriptions.
- Load the clean table into an expense tracker.
Next month, replace the source file or refresh the query, and Excel repeats the same cleanup steps.
Why Power Query is safer than manual cleanup
Manual cleanup is easy to forget. One missed filter or renamed column can change a report without leaving much evidence.
Power Query keeps a visible list of steps, which makes the workflow easier to audit. That matters in small business budgets, sales reports, inventory lists, and project review files where the source data changes frequently.
The query creates a transformed representation of the source; its steps do not rewrite the original file. Edit the query or the source rather than manually editing the loaded output, because a refresh can replace output-only changes.
Build a refreshable query deliberately
- Keep the source location and column names stable.
- Set data types after imports and again after operations that can change types.
- Separate reusable staging queries from final report queries when one long step list becomes hard to review.
- Test refresh with a second representative file, including missing and newly added values.
- Check row counts and totals before connecting the output to a report.
Common Power Query mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Editing the loaded output | Changes disappear on refresh | Edit the query steps instead |
| Leaving types as Any | Dates and numbers behave inconsistently | Set data types explicitly |
| Renaming source columns upstream | Refresh breaks | Keep source exports stable |
| Combining too many jobs | Query becomes hard to review | Split staging and final queries |
NOTE
Do this with AI
Power Query can remove repetitive work, but building the right steps still takes judgment.
"Clean this monthly export the same way each time: remove unused fields, fix amount types, split vendor names, and load a review-ready expense table"
Griddy can help design the cleanup flow, explain each step, and turn messy exports into a structure that feeds templates and reports.
Griddy does not replace Excel's Power Query engine. Use it to reason about the target structure and verify the result, then build and refresh the query in an Excel version that supports the connectors you need.
Related guide: clean data in Excel for one-off formula and worksheet cleanup.
Sources
Do this with AI
Turn the instructions into a finished spreadsheet.
Tell Griddy what you need in plain English, then inspect and keep editing the result in the browser, Excel, or Google Sheets.
Use this on real templates
Turn repeat exports into cleaner template inputs
Power Query is a strong fit when the same source file needs to feed expense, budget, sales, or operating templates every reporting cycle.
Expense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
Open templateFinanceSmall Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.
Open templateSalesSales Pipeline
Track deals by stage, owner, value, and next move in one lightweight pipeline sheet. Keep close dates, weighted forecast, and rep follow-ups visible without needing a full CRM.
Open template