If you use Excel regularly, you almost certainly have access to one of the most powerful data tools ever built into a spreadsheet — and you're probably not using it. Power Query has been included in Excel since 2016, yet the vast majority of business users have never opened it.
That's a significant missed opportunity. In our work with Irish SMEs, Power Query is consistently one of the highest-return tools we introduce. Teams that previously spent hours every week on manual data preparation routinely cut that time to minutes once they learn how it works.
What is Power Query?
Power Query is Excel's built-in data transformation and connection tool. It lets you connect to data sources — files, folders, databases, web pages, SharePoint lists, and more — clean and reshape that data automatically, and load the results into your spreadsheet ready for analysis.
The critical word is automatically. Every step you take in Power Query is recorded as a reusable process. Once you've set it up, you simply click "Refresh" and all your data preparation runs again in seconds — with no manual work at all.
The problem it solves
Think about a typical weekly data task: you export a report from your accounting system, paste it into Excel, delete the columns you don't need, reformat the dates, remove the blank rows, VLOOKUP some customer names from another sheet, and then finally build your summary table. That entire sequence might take 45 minutes every Monday.
With Power Query, you do that sequence once. The tool records every step. Next Monday, you open the file, click Refresh, and the entire process completes in under 30 seconds — including pulling the latest data from the source.
Find it in Excel: Go to the Data tab in Excel and look for "Get Data" or "Get & Transform Data." That's Power Query. If you're using Excel 2016 or later on Windows, it's already there.
Five things Power Query does that manual work can't match
1. Combine multiple files automatically. If you receive weekly or monthly reports as separate files in a folder, Power Query can automatically combine all of them into a single consolidated dataset — and when new files are added to the folder, they're included on the next refresh without any extra work.
2. Unpivot data instantly. Many systems export data in a wide, crosstab format that's hard to analyse in Excel PivotTables. Power Query's unpivot feature reshapes this data into a clean, column-based structure in a single click — a task that can take an hour manually.
3. Clean inconsistent data reliably. Trim extra spaces, standardise capitalisation, split names into first and last, reformat dates, replace error values — Power Query handles all of this consistently, every time, with no risk of human error.
4. Connect live to external data. Power Query can pull data directly from SharePoint, OneDrive, SQL databases, and many web sources. Your Excel reports can refresh against live data rather than requiring a manual export-and-paste cycle.
5. Document your process automatically. Every transformation step is listed and labelled in Power Query's Applied Steps panel. This means your data preparation process is documented by default — something that's rarely true of manual Excel work.
Who benefits most
Power Query delivers the biggest returns for anyone who regularly prepares data for reporting: finance teams running monthly reports, operations teams consolidating data from multiple sites or systems, sales teams tracking pipeline and performance, and operations managers pulling together data from different parts of the business.
If you spend more than two hours per week on data preparation in Excel, Power Query will save you significant time.
The learning curve
Power Query has a visual, point-and-click interface. You don't need to write code to use it — most transformations are available as menu options. A motivated beginner can learn the core capabilities in a half-day of focused practice. The investment pays back quickly.
That said, more complex transformations — merging queries, writing custom M formula steps, handling dynamic parameters — do require more learning and sometimes expert help to implement well.
Want Power Query Working in Your Business?
We can identify your biggest manual data tasks and set up Power Query solutions that save your team hours every week — or train your team to do it themselves.
Book My Free Audit →