Power Query vs Power Automate: which tool for which job
Comparison · By Tahir Nas · Examples run in our simulator · Updated
9 min read
Power Query is for shaping data: it gets data from sources, cleans and combines it, and loads a table into Excel, Power BI or another host. Power Automate is for running a process. When something happens (an email arrives, a file is saved, a schedule fires), it carries out a series of actions across apps, such as saving an attachment, posting a message or starting an approval. If the job ends in a table, start with Power Query; if it ends in an action someone or something has to take, start with Power Automate.
What each tool does
Microsoft describes Power Query as a data transformation and data preparation engine whose transformations are saved as a repeatable query you can refresh. Power Query doesn't store the result itself; the host product decides where it loads (What is Power Query?, Microsoft Learn).
Power Automate is a service for automating business processes and repetitive tasks. Microsoft's description is of automated workflows between apps and services that synchronise files, send notifications and collect data (Explore the Power Automate home page, Microsoft Learn). A flow starts from a trigger and then runs actions, each provided by a connector to an app such as Outlook, SharePoint, Teams or Excel.
The simplest way to tell them apart is to ask what the output is.
| Power Query | Power Automate | |
|---|---|---|
| Output | A table loaded into a host | Actions carried out in other apps |
| Starts when | Someone refreshes, or a scheduled refresh runs in the host | A trigger fires: an event, a schedule or a button |
| Built from | Steps in an M query | A trigger and actions from connectors |
| Good at | Cleaning, reshaping, merging, appending, grouping | Notifications, approvals, moving files, updating records |
| Not for | Sending messages, writing back to sources | Heavy reshaping of tables |
Jobs that belong to Power Query
Anything whose end product is a clean table belongs in Power Query:
- Combining monthly exports into one table.
- Fixing types, dates and numbers written for another country.
- Unpivoting a wide report, filling down labels, removing subtotal rows.
- Matching two lists and finding what's missing from each.
- Building the summary a report or a pivot table reads.
Power Query does these in a few steps that rerun on refresh, and the result is visible and checkable in the editor. Doing the same in a flow means looping over rows with actions and expressions, which is harder to read and to test.
Here is a typical example: a list of invoices that need chasing. The query keeps unpaid invoices whose due date has passed, works out how many days overdue each one is, and sorts the worst first. The report date is fixed in the query so the example gives the same answer every time:
let
ReportDate = #date(2026, 11, 5),
Invoices = #table(
type table [Invoice = text, Customer = text, Due = date, Amount = number, Paid = logical],
{{"INV-101", "Harbour Cafe", #date(2026, 10, 1), 240, false}, {"INV-102", "Mill Books", #date(2026, 10, 20), 95, true}, {"INV-103", "Harbour Cafe", #date(2026, 11, 10), 130, false}, {"INV-104", "Fen Garden", #date(2026, 9, 28), 410, false}}
),
Unpaid = Table.SelectRows(Invoices, each not [Paid] and [Due] < ReportDate),
WithDays = Table.AddColumn(Unpaid, "Days overdue", each Duration.Days(ReportDate - [Due]), Int64.Type),
Sorted = Table.Sort(WithDays, {{"Days overdue", Order.Descending}}),
Result = Table.SelectColumns(Sorted, {"Invoice", "Customer", "Amount", "Days overdue"})
in
ResultResult
| Invoice | Customer | Amount | Days overdue |
|---|---|---|---|
| INV-104 | Fen Garden | 410 | 38 |
| INV-101 | Harbour Cafe | 240 | 35 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Two invoices come back: INV-104 for Fen Garden, 38 days overdue, and INV-101 for Harbour Cafe, 35 days overdue. INV-102 is paid and INV-103 isn't due yet, so both are filtered out. In a live workbook you would replace the fixed date with today's date, for example Date.From(DateTime.LocalNow()), but a fixed report date makes the logic easy to test. The lesson on date parts and durations explains Duration.Days and date subtraction.
Notice what the query doesn't do: it doesn't email anyone. That's the hand-off point.
Jobs that belong to Power Automate
Anything that has to happen because of an event, or that has to do something outside the table, belongs in Power Automate:
- Saving email attachments to a SharePoint folder as they arrive.
- Sending a reminder to each customer on the overdue list.
- Starting an approval when a request form is submitted.
- Posting a Teams message when a file lands in a folder.
- Refreshing a Power BI semantic model (formerly called a dataset) after an upstream file has been updated.
Power Query can't do any of these. It reads sources and loads a result; it doesn't watch for events or act on other systems.
Where the two tools meet
The overlap is real but narrow, and it helps to know exactly where it is.
Power Automate can run Power Query
The SQL Server connector for Power Automate has an action called Transform data using Power Query, which takes M query text and returns the result to the flow. Microsoft's connector reference notes that it doesn't work when connecting with SQL Microsoft Entra ID authentication (SQL Server connector reference, Microsoft Learn). Microsoft's product table also lists Power Automate as a host with the M engine and Power Query Online (What is Power Query?, Microsoft Learn).
This is useful when a flow needs a filtered or reshaped set of rows from SQL Server before acting on them. It isn't a general replacement for a Power Query query in Excel or Power BI.
Power Automate can trigger a refresh
The Power BI connector includes a Refresh a dataset action (the connector still uses the older name for what Power BI now calls a semantic model) and a trigger that fires when a data-driven alert is triggered (Power BI connector reference, Microsoft Learn). That lets a flow refresh a report as soon as its source file has been updated, instead of waiting for the next scheduled refresh, or react when a figure on a dashboard tile crosses a threshold.
Excel automation goes through Office Scripts
For Excel workbooks in OneDrive or SharePoint, flows use the Excel Online (Business) connector, whose Run script actions call Office Scripts (Run Office Scripts with Power Automate, Microsoft Learn). VBA macros can't be called this way: Microsoft's comparison notes that VBA has no Power Automate connector (Differences between Office Scripts and VBA macros, Microsoft Learn). The post on Power Query vs VBA covers when a macro is still the right choice.
A pattern that works: shape, then act
For most reporting processes, the two tools fit together in a clear order:
- Power Query shapes the data. The query builds one clean table, for example the overdue invoices above, with all the business rules in its steps.
- The host stores the result. It loads into an Excel table or a Power BI model, where people can see it.
- The result is refreshed before anyone acts on it. A flow reads the table as it was last refreshed, so a stale table means reminders for invoices that have since been paid (an aged receivables report is a typical source table for them). For Power BI, the flow can run Refresh a dataset first. For Excel, refresh the query before the flow runs, for example with refresh on open in the desktop app, as the post on refreshing Power Query automatically in Excel explains.
- Power Automate acts on it. A scheduled flow reads the finished rows (with the Excel connector, or by querying a Power BI semantic model) and sends reminders, creates tasks or posts a summary.
The important rule is to keep each business rule in one place. If Power Query decides which invoices are overdue, the flow should send reminders for the rows it receives and nothing more. If the flow also filters on dates with its own expression, sooner or later the two definitions drift apart and the reminders stop matching the report.
How to choose
Ask three questions about the job:
- Is the end product a table? Use Power Query.
- Does something need to happen because of an event or on a schedule, outside the table? Use Power Automate.
- Both? Build the table in Power Query first, then have Power Automate act on the finished rows.
If you're new to the data side, the Power Query for beginners guide covers the editor and the core steps. To understand how and when queries refresh in each host, read the lesson on refresh in Excel, Power BI Desktop and the service. And to practise the filtering logic from the example, try the exercise to calculate days to close each ticket on Power Query Exercises, which runs in the browser and checks your answer against hidden test cases.
Frequently asked questions
Can Power Automate replace Power Query?
Not for data preparation. A flow can loop over rows and apply expressions, but reshaping, merging and unpivoting are far easier to build, read and check as Power Query steps. Use Power Automate to act on data, and Power Query to prepare it.
Can Power Query send an email or move a file?
No. Power Query reads from sources and loads a result into its host. Sending messages, moving files and updating records are jobs for Power Automate or another automation tool.
Do I need a separate licence for Power Automate?
Licensing depends on your Microsoft 365 plan and on which connectors a flow uses, and it changes over time. Check your organisation's licences in the Power Automate settings, or ask your administrator, before planning a flow that relies on a particular connector.
Sources
- What is Power Query?, Microsoft Learn (learn.microsoft.com)
- Explore the Power Automate home page, Microsoft Learn (learn.microsoft.com)
- SQL Server connector reference, Microsoft Learn (learn.microsoft.com)
- Power BI connector reference, Microsoft Learn (learn.microsoft.com)
- Run Office Scripts with Power Automate, Microsoft Learn (learn.microsoft.com)
- Differences between Office Scripts and VBA macros, Microsoft Learn (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Power Query vs VBA macros: when to switchWhen to replace a VBA macro with Power Query: which data jobs move across, which still need VBA, and how to migrate a macro without breaking the report.
- Power Query vs Python pandas for data cleaningPower Query or pandas for cleaning data? The same tasks side by side (unpivot, merge, locale parsing), where each tool fits, and how they work together.
- How to refresh Power Query automatically in ExcelRefresh Power Query when an Excel workbook opens, every few minutes or on a timer with VBA, and make the queries safe to refresh when nobody is watching.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.