What Is Power Query? The Excel and Power BI Data Tool Explained
Power Query is Microsoft's built-in tool for connecting to data, cleaning it up and reshaping it, without writing formulas or scripts. This guide explains what Power Query is in Excel and Power BI, what it's used for, how query folding makes it fast, and how it fits into wider business reporting.
Quick answer: what is Power Query?
Power Query is a data connection and transformation tool built into Excel and Power BI. It connects to a data source, such as a spreadsheet, database or website, and lets you clean, reshape and combine that data through a point-and-click interface before it ever reaches a worksheet or report, with every step recorded and repeatable.
- In Excel: Power Query is the "Get & Transform Data" feature on the Data tab, used to pull in and clean data before analysis.
- In Power BI: Power Query is the data preparation layer behind every report, accessed through Power Query Editor.
- What it's used for: combining files, removing duplicates, reshaping columns, merging tables and automating data clean-up that would otherwise take manual, repeated work.
- Query folding: where possible, Power Query pushes its steps back to the source system (such as a SQL database) to run there, rather than pulling all the raw data in first, which makes refreshes much faster.
1. What Is Power Query?
Power Query is Microsoft's data connection and transformation engine. Instead of manually copying data between spreadsheets, writing nested formulas to clean it up, or asking someone in IT to run a script, Power Query lets you connect directly to a data source and apply a series of transformation steps through a visual interface.
Every action you take, removing a column, filtering rows, splitting text, merging two tables, is recorded as a step. Refresh the query later and Power Query repeats every step automatically against the latest data. This is what separates it from a one-off manual clean-up: the work is done once and reused every time the data changes.
Power Query is not a separate product you buy. It is the same engine built into Excel, Power BI, Power Apps, Power Automate, Dataverse and Microsoft Fabric, which is why the skills transfer directly between them.
↑ Back to Contents2. What Is Power Query in Excel?
In Excel, Power Query sits under Data > Get & Transform Data (sometimes still labelled "Get Data"). It is the tool behind phrases like "importing and cleaning data" or "Get & Transform," and it has been part of Excel since Excel 2016, after starting life as a separate add-in for Excel 2010 and 2013.
A typical use in Excel is pulling in several CSV exports from a finance system, each with slightly inconsistent column names and formats, and combining them into one clean table ready for a PivotTable. Once that query is built, next month's export can be refreshed into the same clean shape in seconds, rather than being rebuilt from scratch.
Power Query vs formulas and VLOOKUP
Formulas calculate values live within a worksheet and recalculate constantly. Power Query instead transforms and loads data once per refresh, before it reaches the worksheet. For repeatable clean-up of messy, changing data, Power Query is usually far more maintainable than a web of nested formulas.
3. What Is Power Query in Power BI?
In Power BI Desktop, Power Query is accessed through Home > Transform Data, which opens the Power Query Editor. Every Power BI report starts with Power Query, because it is the layer that decides what data gets into the model and in what shape, before any visuals or DAX measures are built on top of it.
Power Query also powers Dataflows in the Power BI service, which let an organisation build a shared set of cleaned, reusable queries once and have multiple reports draw on them, rather than each report builder reinventing the same clean-up steps. In Microsoft Fabric, the same engine sits behind Dataflow Gen2, extending Power Query to larger-scale data pipelines.
↑ Back to Contents4. What Is Power Query Used For?
Asked plainly, what is Power Query used for day to day? In both Excel and Power BI, it tends to handle the same handful of jobs.
Combining files from a folder
Pulling in dozens of CSV or Excel exports from a single folder and stacking them into one consistent table, without opening each file by hand.
Cleaning messy data
Removing blank rows, trimming whitespace, fixing inconsistent capitalisation and correcting data types, such as text that should be a date or number.
Merging and appending tables
Joining two tables on a common column (merge) or stacking similarly structured tables on top of each other (append), the Power Query equivalent of a VLOOKUP or UNION.
Reshaping data
Pivoting and unpivoting columns, splitting one column into several, or grouping and summarising rows, to get data into the shape a report or PivotTable actually needs.
Connecting to databases and web data
Pulling data directly from SQL Server, SharePoint lists, Dataverse, web pages and over 150 other connectors, instead of relying on manual exports.
Repeatable, automated refreshes
Recording every clean-up step once, so refreshing the query applies the same logic to new data automatically, which is the main time saving over manual spreadsheet work.
5. Power Query, Applied Steps and the M Language
Every action you take in Power Query's visual interface, filtering, renaming, merging, appears in a panel called Applied Steps. You can reorder them, edit them or delete them, and the preview updates live. Behind the scenes, each step is written in a functional language called M (sometimes called the Power Query Formula Language).
Most users never need to write M directly, since the point-and-click interface generates it automatically. But every query is, underneath, a let...in expression made up of named steps, and the Advanced Editor lets more experienced users read or edit that code directly for complex logic the interface doesn't expose.
let
Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
FilteredRows = Table.SelectRows(Source, each [Region] = "North"),
RenamedColumns = Table.RenameColumns(FilteredRows, {{"Qty", "Quantity"}})
in
RenamedColumns6. What Is Query Folding in Power BI and Excel?
Query folding is the process by which Power Query translates your applied steps into a single native query, such as SQL, and sends it back to the source system to run there, instead of pulling in all the raw data and transforming it locally.
If a query against a SQL database filters rows, renames columns and removes duplicates, and every one of those steps can fold, Power Query combines them into one SQL statement that the database executes. The database does the heavy lifting using indexes and its own optimised engine, and only the smaller, already-filtered result set is sent back to Excel or Power BI. This is why query folding matters so much for performance: without it, the full, unfiltered dataset has to travel across the network and be processed locally, step by step.
Check whether a step still folds
Right-click any step in Applied Steps and look for "View Native Query." If it's available (not greyed out), that step and everything before it has folded back to the source.
Know what breaks folding
Certain steps, such as custom M functions, some merges across different source types, and changes applied after a step that already broke folding, force the rest of the query to run locally in Power Query's own engine.
Order your steps deliberately
Placing foldable steps (filters, column selection, renames) early and non-foldable or custom steps later keeps as much work as possible happening on the source system.
Query folding only applies to sources that can be queried
Folding relies on the source system understanding a query language, so it works with databases such as SQL Server and OData services. Flat files like CSV, PDF or a fixed Excel range have nothing to fold back to, since there's no query engine on the other end, so every step runs inside Power Query's own mashup engine instead.
7. What Data Sources Can Power Query Connect To?
Power Query ships with well over 150 built-in connectors, covering the sources most businesses actually use.
| Category | Examples |
|---|---|
| Files | Excel workbooks, CSV, text, XML, JSON, folders of files |
| Databases | SQL Server, Azure SQL, Access, Oracle, PostgreSQL, MySQL |
| Microsoft 365 & Dynamics | SharePoint lists, Dataverse, Dynamics 365, Exchange |
| Online services | Web pages, OData feeds, Salesforce, Google Analytics |
| Azure & big data | Azure Data Lake, Azure Synapse, Databricks |
Custom connectors can also be built with the Power Query SDK for systems without a built-in option, which is common where a business runs older, in-house line-of-business software.
↑ Back to Contents8. Where Power Query Reaches Its Limits
Power Query is a transformation tool, not a reporting or security platform, and it's worth being clear about where it stops.
✔ Power Query is well suited to
- Repeatable data clean-up from the same sources, refreshed on a schedule.
- Combining several files or tables into one consistent dataset.
- Preparing data before it reaches a PivotTable, chart or Power BI report.
✘ Power Query is not a substitute for
- Row-level security or access control: that sits in Power BI's own security model, not Power Query.
- Live, transactional calculations: Power Query is static after refresh; use DAX measures in Power BI for anything that must react instantly to a filter or slicer.
- Very large-scale data engineering: at enterprise volumes, dedicated pipelines in Microsoft Fabric or Azure Data Factory often take over from desktop-level Power Query.
9. From Spreadsheets to Proper Reporting
Power Query is often where a business's reporting starts to outgrow spreadsheets. Once several Power Query-fed workbooks are being emailed around and manually refreshed each month, that is usually the sign that the same queries, and the data behind them, belong in a proper Power BI model with scheduled refresh and a single shared source of truth.
A natural next step for many businesses is connecting Power Query directly to Microsoft Dynamics 365 Business Central, so financial and operational data flows straight into Power BI without manual exports at all. From there, dashboards can be shared, scheduled to refresh automatically and secured by role, rather than passed around as static spreadsheets. Our Power BI services team builds exactly this kind of reporting, from early-stage Power Query clean-up through to full Power BI models.
Where the data clean-up itself needs to run on a schedule, without someone opening Excel to hit refresh, that automation overlaps with Power Automate, which can trigger refreshes, move files into the right folder structure, or notify a team when a report is ready.
↑ Back to Contents10. Frequently Asked Questions
What is Power Query?
Power Query is Microsoft's data connection and transformation engine, built into Excel, Power BI and other Microsoft 365 tools. It connects to a data source, applies a series of recorded clean-up and reshaping steps, and loads the result ready for analysis or reporting.
What is Power Query in Excel?
In Excel, Power Query is the "Get & Transform Data" feature on the Data tab. It lets you import data from files, databases or the web, clean and reshape it, and load it into a worksheet or the Excel data model, with the steps saved so the query can be refreshed later.
What is Power Query in Power BI?
In Power BI, Power Query is accessed through Home > Transform Data, opening the Power Query Editor. It is the data preparation layer that every Power BI report is built on, deciding what data enters the model and in what shape before visuals and DAX measures are created.
What is Power Query used for?
Power Query is used to combine files from a folder, clean messy or inconsistent data, merge and append tables, reshape columns through pivoting and grouping, and connect to databases, SharePoint, web pages and other sources, with every step repeatable on refresh.
What is query folding in Power BI?
Query folding is the process where Power Query translates your transformation steps into a single native query, such as SQL, and sends it to the source system to run there instead of locally. This reduces the data transferred and speeds up refreshes, but it only works with sources that support querying, such as databases, not flat files like CSV.
How do I know if my query is folding?
Right-click a step in the Applied Steps panel and check whether "View Native Query" is available. If it is, that step and everything before it has folded back to the source. Once a step breaks folding, every step after it runs locally in Power Query's own engine.
What is the M language in Power Query?
M, also called the Power Query Formula Language, is the functional language that every Power Query step is written in behind the scenes. The point-and-click interface generates M automatically, but it can also be viewed and edited directly in the Advanced Editor for more complex transformations.
Is Power Query the same as Power BI?
No. Power Query is the data connection and transformation engine, while Power BI is the wider business intelligence platform for building reports and dashboards. Power Query is one component inside Power BI, as well as being built into Excel and other Microsoft 365 tools separately.
Do I need to know how to code to use Power Query?
No. Power Query's interface is built around clicking buttons such as Remove Columns, Merge Queries and Group By, with each action recorded automatically as a step. Writing or editing M code directly is only needed for more advanced, custom transformations.
Turn Your Spreadsheets Into Proper Reporting
As a Microsoft Solutions Partner and managed IT services provider, we help businesses move from manual Power Query spreadsheets to connected Power BI dashboards and automated reporting.
Speak With a Power BI Consultant 0161 834 9345
