What Is Power Query — and How Can It Save Your Business Time?

The short answer

Power Query is a data transformation tool built into Excel and Power BI. It connects to your data sources — spreadsheets, accounting software, databases, CSVs — pulls the data automatically, cleans and formats it according to rules you set once, and delivers it ready to use. Every step is recorded and can be replayed automatically whenever new data arrives. No formulas. No macros. No manual reformatting.

If you have ever spent time reformatting a CSV export before you could use it, copying data from one spreadsheet to another, cleaning dates that came out in the wrong format, or removing blank rows from a dataset — Power Query can do all of that automatically, every time, without you touching it.

It is not widely known outside of data teams and specialist Excel users, which means most UK businesses are doing work manually that they could be doing automatically. This guide explains what Power Query actually does and where it saves the most time in a typical business.

Power Query Excel automation UK business data transformation

What Power Query actually does

Power Query does three things: it connects to data sources, it transforms the data, and it loads the result into Excel or Power BI. The key point is that every transformation step you apply is recorded as a step in a query. When you click refresh, Power Query replays all those steps automatically against the latest data.

You build the query once. After that, getting updated data is a single click — or happens automatically on a schedule.

What you can connect it to

Power Query has over 100 built-in data connectors. The most commonly used for UK businesses include:

Excel files and CSV exports

Connect to any Excel file or CSV — including ones that other people update — and pull the data automatically.

Xero, Sage, QuickBooks

Connect directly to your accounting software via the API and pull financial data without manual exports.

SharePoint and OneDrive

Connect to files stored in SharePoint or OneDrive — useful when multiple people update a shared spreadsheet.

SQL databases

Query a database directly and pull exactly the data you need, already filtered and formatted.

Web data and APIs

Pull data from websites or APIs — exchange rates, market data, or any third-party system with an API.

Google Sheets

Connect to Google Sheets and pull the data into Excel automatically, keeping the two in sync.

What transformations it can apply automatically

Once connected to a data source, Power Query can apply any transformation automatically every time the data refreshes:

✓

Fix date formats

Accounting software exports often produce dates in formats Excel does not recognise. Power Query converts them automatically to whatever format you need.

✓

Remove blank rows and columns

Exports often include blank rows, headers in the wrong place, or summary rows at the bottom. Power Query removes them automatically.

✓

Standardise text

Customer names entered in different ways, category codes with inconsistent capitalisation, columns with trailing spaces. Power Query standardises all of these according to rules you define.

✓

Split and merge columns

A full name in one column that needs to be first name and last name separately. A date and time in one column that needs to be split. Power Query handles both.

✓

Filter rows

Pull only the rows that meet specific criteria — only invoices over a certain amount, only transactions from the current month, only customers in a specific region.

✓

Combine multiple sources

Pull data from two or more sources and combine them into a single table. An accounting export and a CRM export combined into one view of customer revenue and pipeline.

Power Query transformations saving time UK business Excel automation

Power Query vs Excel formulas — what is the difference?

Excel formulas are applied to specific cells and depend on the data being in the right place at the right time. When the data structure changes — a new row, a new column, a different number of entries — formulas often break and need to be fixed.

Power Query is applied to the data source before it enters the spreadsheet. The transformations are defined once and applied automatically regardless of how many rows or columns the data contains. A query that works for 100 rows works equally well for 10,000 rows. A query that handles January data handles December data without any modification.

For repetitive data cleaning and transformation tasks — the kind that happen every week or every month — Power Query is almost always more robust than formulas. It does not break when the data structure changes. It does not depend on cells being in specific positions. And it can be refreshed automatically without anyone opening the file.

Related: Power Query is one of the primary tools we use when automating Excel reports for UK businesses. It also forms the connection layer when connecting Xero to Excel automatically.

Frequently asked questions

Do I need to know how to code to use Power Query?

No. Power Query has a visual, point-and-click interface where you apply transformations by selecting options from menus. It records those steps as M code in the background, but you do not need to read or write any code to use it effectively. More advanced Power Query work — custom functions, complex conditional logic — does involve writing M code, which is where a consultant adds value.

Is Power Query available in all versions of Excel?

Power Query is available in Excel 2016 and later as a built-in feature, accessed via the Data tab. In Excel 2010 and 2013 it was available as a free add-in. If you are on a current Microsoft 365 subscription, you already have full Power Query capability.

Can Power Query refresh automatically without me opening Excel?

Power Query refreshes when you open the file or when you click refresh manually. For fully automatic scheduled refreshes — where the data updates without anyone opening the file — you need either Power BI (which has built-in scheduled refresh) or Power Automate to trigger the refresh on a schedule.

What is the difference between Power Query and Power BI?

Power Query is a data transformation tool that exists inside both Excel and Power BI. Power BI is a complete business intelligence platform for building dashboards and reports. When you use Power BI, you use Power Query to connect to and transform your data before it goes into the dashboard. They are related but different things.

Can Power Query handle large datasets?

Power Query handles data significantly larger than raw Excel can manage comfortably. It processes transformations efficiently and, when combined with Power Pivot, can work with millions of rows. For very large datasets where performance matters, Python is more powerful — but for most UK SME use cases, Power Query handles the volume without issue.