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.
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.
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
The Griddy way
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.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and 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