Choose Power Query when the output has to land in an Excel sheet or a Power BI model and a person, or a gateway, will refresh it. Choose Python when the job has to run with nobody at the desk, or when the result must go somewhere other than a sheet, such as files, email, an API or forty other workbooks. Most real builds use both, with Python moving the data and Power Query shaping it.
Last updated 3 September 2026 · by Inam Ul Haq, data analyst and automation engineer · about the author
Every comparison of these two tools ranks them on the same two axes, how easy they are to learn and how powerful they are. Both answers are true and neither helps you, because you are not choosing a tool for life. You are choosing the first tool for one specific job, and the job has a shape that decides it before you open either program.
I use both every week. Power Query inside the client workbooks and Power BI models I hand over, Python for the report robots that run after my day job while I am asleep. The choice has stopped being about fandom and become three plain questions, and the two things nobody writes down are the ones that settle most cases.
Nobody asks who presses Refresh at seven in the morning. That question decides more builds than any feature list.
Which three questions decide it before you touch either tool?
Forget the feature comparison for a moment and describe the job as a shape. Where does the data come from, where must it end up, and who is standing there when it runs. Three questions cover almost every automation I have been asked to build.
First, who presses Refresh. A Power Query inside a desktop workbook runs when a person opens the file and refreshes it, or when a macro does it for them. There is no scheduler inside Excel on your desk, so if the honest answer is that nobody will be there, the query is already the wrong first tool, and a Power Automate flow will not refresh it for you either.
Second, where the output must land. In Excel and Power BI the result of a query can only arrive in a worksheet table or a data model. It cannot write a file, rename one, send a mail, post to an API or open another workbook and change it. If the deliverable is anything but a table inside the file the query lives in, that is a Python job or a Python plus something job.
Third, who inherits it. A query is a list of visible steps an Excel person can click through and re-record without ever reading code. A script is a text file that someone has to be able to read when it stops at six in the evening with a traceback. Pick for the person who will own it next year, not for yourself this week.
| The job as it actually arrives | First tool | Why | Where it breaks |
|---|---|---|---|
| Ten exports with the same columns into one sheet | Power Query | Folder combine, no code | One file with a different column set |
| The same report every morning, nobody at the desk | Python | Task Scheduler runs it headless | The machine has to be on |
| Read the table out of a text based PDF | Power Query | Native PDF connector | Long descriptions split rows |
| Write results back into forty workbooks | Python | A script can open and save files | Formulas in those files need care |
| Feed a Power BI model that refreshes in the service | Power Query | It is the model layer already | Local files need a gateway |
| Call an API with paging and a token, then email a summary | Python | Requests, loops and mail in one file | Somebody keeps the token alive |
| A cleaning rule the analyst must be able to change | Power Query | Steps are visible and clickable | Edits the UI cannot express |
Notice that the shape, not the size, drives the table. A hundred thousand rows into one sheet is a Power Query job. Ten rows that have to become ten emails is a Python job. Volume only matters after the shape has been settled.
When does Power Query win outright?
Power Query wins when the whole job is read, reshape, land. Many sources with the same structure, a fixed set of transformations, and an output that stays inside Excel or Power BI where a person will look at it. That covers a large share of what people call automation, and for that share it is the better tool, not the beginner tool.
The folder combine is the clearest case. Point it at a folder of monthly exports and it builds one table from all of them, with a sample query you can edit once and have applied to every file. Microsoft states the one condition plainly in its combine files documentation: the files must share the same type and structure, including the same columns. Break that and the combine breaks, which is honest behaviour, but it means a vendor renaming one column ends your morning.
The second case is anything that feeds a model. If the destination is a Power BI report, the shaping belongs in the query layer because that is where the model expects it, and it is the same layer that builds the fact and dimension tables in a star schema. The third case is the text based PDF, because the PDF connector is already in your Excel and already paid for, which is the whole first tier of pulling invoice data without retyping.
- 01It fails in the good direction on missing columnsA step that names a column which has vanished stops the refresh with an error naming that column. That is the loud failure you want. An extra new column, by contrast, usually slides through silently unless you removed other columns explicitly, so do that on purpose.
- 02The build travels inside the fileHand the workbook over and the automation goes with it. No install, no environment, no path to a script that was on your laptop. For a client who will own the file, that matters more than any feature.
- 03The analyst can change a rule without youEach step is visible and clickable, so a filter or a rename is a two minute change by the person who understands the data. The price is that anything the interface cannot express becomes hand written M, which very few analysts want to inherit.
When does Python win outright?
Python wins the moment the job leaves the sheet. It runs from Task Scheduler at half past six with nobody logged in to Excel. It writes a fresh workbook, renames the inputs, archives them, emails the summary, and stops loudly with a message when a column is missing. None of that is available to a query, and no amount of skill with M changes it.
The second win is writing back. If the deliverable is forty regional workbooks each holding its own slice, or one template filled forty times, a script opens each file and saves it. The one edge to respect is formulas: a script writes values and formula text, and it does not calculate, so a file that must show computed results has to be opened in Excel once or built without depending on the calculation.
The third win is logic with state. Anything that loops with a memory, calls an API page by page with a token, matches names fuzzily with a threshold you tune, or has to be tested against known inputs before it runs on real ones, is easier in a text file that a test can call. And a text file lives happily in git, which is the version control the query never had.
- Check the columns of every input before touching the data, and stop with the missing column named
- Write the output to a new file every run, so a bad run never overwrites a good one
- Log one line per run with the date, the input files and the row counts, so a silent failure is visible
- Keep the script beside the data it serves, in a folder the inheritor can find
- × Reach for Python because it feels more serious, when a folder combine would carry you for years
- × Depend on a package that only exists in your environment, then hand over the file alone
- × Let the script fill a missing value with a default and carry on, which is the quiet lie that adds up
- × Schedule it on a laptop that goes to sleep at night
The honest cost is the environment. A script needs a machine with Python installed, the packages it imports, and a person who can read the error when something upstream changes. If the person inheriting the build is not that person, the script is the wrong choice even when it is technically the better one. If that person is you and you are not there yet, the fast route is learning enough Python with AI beside you, and the project ladder I use for that is From Zero to Python Expert.
Does Python in Excel change the answer?
Not for automation, and the reason is worth knowing because the name suggests otherwise. Python in Excel runs your code in a container on the Microsoft Cloud, not on your machine. Microsoft states in its data security note for Python in Excel that the code does not have network access and that the container lives only while the workbook is open.
So it cannot see the folder on your desk where the exports land, cannot write a file, and cannot run while the workbook is closed. It is a very good analysis layer for data that is already in the sheet, and I use it for exactly that. It is not a way to make a query run at seven with nobody there.
The same question applies to the query itself, so here is the honest map of every route to an unattended run. Each one works, and each one adds a condition that the comparison articles leave out.
| Route | What actually runs it | The condition attached |
|---|---|---|
| Power Query in a desktop workbook | A person, refresh on open, or a macro | Excel has to be open on a machine |
| Power Query in the Power BI service | Scheduled refresh in the service | Local files and databases need a gateway running on a machine |
| Python script on Task Scheduler | Windows, at the time you set | The machine is on, Python and packages installed |
| Python in Excel | A Microsoft Cloud container | No network, no local files, only while the workbook is open |
The Power BI route is real and I use it for client dashboards, but read the condition. Microsoft's own scheduled refresh documentation is built around the on-premises data gateway, because a file on your network is not visible to the service without one. That gateway is a service on a machine that must stay on, which is the same condition the Python script carries, just wearing different clothes.
How do the two work together in one build?
The builds that last are rarely one tool. The split that keeps working for me is by zone. Python owns everything outside the workbook, and Power Query owns everything inside it. Draw the line at a single clean file and both sides get simpler.
- 01Python collects and checksIt gathers the exports from wherever they arrive, verifies the columns, archives the originals under a dated name and stops loudly if a file is wrong. This is the messy work and it runs on a schedule.
- 02Python writes one clean fileA single CSV with fixed columns and typed dates, in one folder, replaced every run. Nothing downstream ever sees the raw exports again.
- 03Power Query loads and shapesThe workbook or the model reads that one file. The analyst owns the filters, the lookups and the layout, and can change them without touching the script or asking you.
- 04A person or a gateway refreshesBecause the query only reads one predictable file, the refresh is fast and boring, which is what you want from the step a human presses.
This is also the shape that survives handover. The client keeps an Excel file that behaves like an Excel file, and the script is a separate thing with a separate owner that can be replaced without touching the workbook. The same rule I keep repeating in the piece on report automation applies here: pick the lowest tier that survives your real files, and the lowest tier is usually a query with a script feeding it.
Where it does not work is when the two zones fight over the same job. A script that opens Excel through automation to press Refresh for a query, then reads the result back, is the fragile version, and it breaks the day a dialog box appears. Either the script does the shaping itself or the query does, and the handshake between them is a file, never a running copy of Excel.
And sometimes the answer is that neither tool is due yet. The third time you clean the same file by hand is the signal I described in cleaning messy Excel data. Before that third time, your notes are the automation, and a query built on a job you cannot yet describe in plain rules is a query you will rebuild. If the shape is genuinely mixed and you want a second opinion before committing to either, start a project and describe the job as a shape, who runs it and where it must land, and I will tell you which tool goes first, even when the answer is a folder combine and no project at all.
The order, then: describe the job as a shape before you open a tool. If a person refreshes it and it lands in a sheet or a model, start with Power Query, and let the folder combine or the PDF connector carry you. If it has to run with nobody there, or the result has to leave the sheet, start with Python and give it a machine that stays awake. When it is both, split by zone and make one clean file the handshake.
Neither tool is the grown up one. The grown up decision is picking for the person who owns the build next year, and being honest about who will be standing there at seven when it runs. If you want the scripts and templates I build along the way, the membership includes every product I release, current and future. The shape of the job was always the real answer.