Power QueryExercises

Power Query vs Python pandas for data cleaning

Comparison · By Tahir Nas · Examples run in our simulator · Updated

9 min read

Use Power Query when the cleaned data is going into Excel or Power BI and the people maintaining it work in those tools. Use pandas when the cleaning is part of a larger Python workflow, needs statistics or custom logic beyond tabular reshaping, or has to run outside Microsoft's apps. For the everyday jobs (fixing types, unpivoting, merging, grouping) both tools are capable, and the deciding factors are where the result lives and who has to keep it running.

What each tool is

Power Query is a data transformation and preparation engine built into Excel, Power BI and other Microsoft products. You build a query by clicking through the editor, and each action becomes a step in an M script that runs again on refresh (What is Power Query?, Microsoft Learn).

pandas is an open-source Python library for working with tabular data. Its central object, the DataFrame, is a table of rows and columns, and you clean data by writing Python that calls DataFrame methods. You need a Python installation (or a hosted notebook) to run it.

That difference, an engine inside an application versus a library inside a programming language, explains most of what follows.

The same tasks side by side

The examples below put a Power Query step next to the pandas call that does the same job. The M blocks were run in this site's simulator, and the output described is what it returned. The pandas snippets are shown for comparison and follow the pandas documentation; they were not run here.

Unpivot a wide report

A common mess is one column per month. Power Query turns the month columns into rows with Table.UnpivotOtherColumns, which keeps the columns you name and unpivots everything else:

let
    Source = #table({"Store", "Jan", "Feb", "Mar"}, {{"Leeds", 120, 135, 128}, {"York", 90, null, 101}}),
    Long = Table.UnpivotOtherColumns(Source, {"Store"}, "Month", "Units")
in
    Long

Result

StoreMonthUnits
LeedsJan120
LeedsFeb135
LeedsMar128
YorkJan90
YorkMar101

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The result has five rows, not six. York's February value was null, and unpivot drops null cells, so there is no York–Feb row. That behaviour is the same whether you click Unpivot Other Columns or write the step, and it is worth knowing before you count rows. The unpivot guide covers how to keep those rows when they matter.

In pandas, the equivalent is melt, which keeps identifier columns and stacks the rest into variable and value columns (Reshaping and pivot tables, pandas documentation):

long = df.melt(id_vars=["Store"], var_name="Month", value_name="Units")

One difference to watch: the melt reference lists no option for dropping missing values, unlike Power Query's unpivot. If you want the Power Query result, filter the empty values out afterwards:

long = long.dropna(subset=["Units"])

Merge two tables

Power Query merges with Table.NestedJoin and then expands the columns you want. A left outer join keeps every order, even one whose customer isn't in the lookup:

let
    Orders = #table({"Order", "Customer"}, {{1, "C1"}, {2, "C2"}, {3, "C9"}}),
    Customers = #table({"Customer", "City"}, {{"C1", "Leeds"}, {"C2", "York"}}),
    Joined = Table.NestedJoin(Orders, {"Customer"}, Customers, {"Customer"}, "Match", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "Match", {"City"})
in
    Expanded

Result

OrderCustomerCity
1C1Leeds
2C2York
3C9null

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

All three orders come back, and order 3 has a null City because customer C9 has no match. The lesson on choosing a join kind explains which kind to pick and how to find the unmatched rows deliberately.

pandas does the same with merge, whose how parameter takes left, right, outer, inner, cross and anti-join options (pandas.merge, pandas documentation):

joined = orders.merge(customers, how="left", on="Customer", validate="many_to_one")

The validate argument is a habit worth copying into Power Query. With many_to_one, pandas checks that the key is unique in the right-hand table and raises an error if it isn't. Power Query has no single switch for that; you check key uniqueness yourself before merging, as the lesson on checking key quality shows.

Read numbers and dates written for another country

European exports often write 1.234,50 for one thousand two hundred and thirty-four and a half, and dates day-first. Power Query handles both by changing type with a culture:

let
    Source = #table({"Invoice", "Amount", "Date"}, {{"A1", "1.234,50", "03/04/2026"}, {"A2", "99,00", "28/02/2026"}}),
    Typed = Table.TransformColumnTypes(Source, {{"Amount", type number}, {"Date", type date}}, "de-DE")
in
    Typed

Result

InvoiceAmountDate
A11234.52026-04-03
A2992026-02-28

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The simulator returned 1234.5 and 99 for the amounts, and 3 April 2026 and 28 February 2026 for the dates. The culture applies to every column in that step, which is convenient when a whole file comes from one country.

pandas handles the same file at read time. read_csv has decimal, thousands and dayfirst parameters, and dtype can force a column such as a postcode or order number to stay as text (pandas.read_csv, pandas documentation):

df = pd.read_csv("invoices.csv", decimal=",", thousands=".", dayfirst=True, parse_dates=["Date"])

Both approaches work. Power Query's version lives in a step you can see and change later; pandas puts the decisions in the read call.

Where the differences really are

On the transformations themselves, the two tools are closer than people expect. The real differences are around them.

QuestionPower Querypandas
Where does it run?Inside Excel, Power BI and other Microsoft hostsWherever Python runs: a laptop, a server, a notebook
How is work recorded?As steps in an M script, visible in Applied StepsAs Python code in a script or notebook
Who can maintain it?Anyone comfortable in the Power Query editorSomeone who can read and run Python
How is it refreshed?Refresh button, or scheduled refresh in the Power BI serviceHowever you schedule the script
InstallationNothing extra in Excel or Power BI DesktopA Python environment and packages
Beyond cleaningShaping for a data model or worksheetStatistics, machine learning, plotting, any Python library

Repeatability

Power Query is built around refresh. Microsoft describes each query as a repeatable process you can refresh to get up-to-date data (What is Power Query?). A pandas script is just as repeatable, but you supply the scheduling, the environment and the place the output goes.

Scale and performance

When Power Query reads from a database, it can push steps back to the source through query folding, so filtering and grouping happen in the database rather than on your machine. Files such as CSV and Excel don't fold (Understanding query evaluation and query folding, Microsoft Learn). pandas loads data into memory on the machine running the script, so very large data needs either a bigger machine, chunked reading, or a different tool. Neither tool is fast or slow in the abstract; it depends on the source and the steps.

Readability for the next person

The Applied Steps pane is a list of named steps that a colleague can click through and inspect. A well-written pandas script can be just as clear, but only to someone who reads Python. In a finance or operations team where nobody else codes, that is often the deciding factor.

Using both together

You don't always have to choose. Power BI Desktop has a Python script connector, and Microsoft documents its rules: you need a local Python installation with the pandas and Matplotlib packages, only pandas data frames are imported, scripts that run longer than 30 minutes time out, and nested tables aren't supported (Run Python scripts in Power BI Desktop, Microsoft Learn). Refreshing the report in Desktop runs the script again. For scheduled refresh in the Power BI service, Microsoft's personal gateway documentation says to install the matching Python version and the required packages on the gateway machine, and to keep that machine on and the gateway signed in during refresh (Use a personal gateway in Power BI, Microsoft Learn).

That makes a sensible split possible: Python for the part that genuinely needs it (a statistical model, a library with no Power Query equivalent), Power Query for the routine shaping around it. Keep the Python part small. Every report that depends on a script also depends on a working Python installation wherever it refreshes.

How to choose

Pick Power Query when:

  • The result lands in an Excel workbook or a Power BI model.
  • The people who will maintain it use Excel or Power BI, not Python.
  • The job is mostly reshaping: types, columns, filters, merges, appends, unpivots, group by.
  • You want a refresh button rather than a script to run.

Pick pandas when:

  • Cleaning is one stage of a larger Python pipeline or analysis.
  • You need statistics, machine learning or libraries outside Microsoft's tools.
  • The process must run on a server or in a scheduled job with no Excel or Power BI involved.
  • Your team already reviews and versions Python code.

If you are an Excel or Power BI user deciding what to learn first, Power Query usually pays off sooner, because it works inside the tools you already use. The post on whether Power Query is worth learning looks at who gains most. The Power Query for beginners guide is a good starting point, and if you already know SQL, the SQL to Power Query M translation guide maps familiar ideas across. For where database work fits, see Transform in SQL, Power Query or DAX?.

To practise the comparisons above in Power Query, try the unpivot month columns exercise and the merge exercise that finds orders from unknown customers. Both run in the browser on Power Query Exercises and check your result against hidden test cases.

Frequently asked questions

Is pandas faster than Power Query?

It depends on the source and the steps. Power Query can fold work back to a database, which can be much faster than loading everything; pandas works in memory on the machine running it. For everyday file sizes, measure before switching tools: step order and unnecessary work are often the cause of slowness, so profile the query or script first.

Can I use Python inside Power Query?

In Power BI Desktop, yes: the Python script connector runs a script and imports its pandas data frames, with the limits Microsoft documents (30-minute timeout, no nested tables). In Excel, Python in Excel is a separate feature: you write Python in worksheet cells, and Microsoft says those calculations run in the Microsoft Cloud (Introduction to Python in Excel, Microsoft Support).

Should a data analyst learn Power Query or pandas first?

Learn the one that matches where your work ends up. If your reports live in Excel or Power BI, start with Power Query. If you already work in notebooks or need statistics, start with pandas. Many analysts end up using both, and the cleaning ideas (types, keys, tidy tables) carry across. For a planning timeline on the Power Query side, see how long it takes to learn Power Query.

Sources

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

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.