Get External Data into Python in Excel with Power Query

The first thing people try in a =PY() cell is pd.read_csv("C:/data/sales.csv") or requests.get(...), and both fail. Python in Excel runs in an isolated container in Microsoft's cloud: it has no view of your disk and no internet access, by design. Everything it analyses must already be in the workbook. The supported way to get it there is Power Query — Excel's built-in import engine, under Data → Get Data — which can pull from files, folders, databases and web sources, refresh on demand, and hand the result to Python by name. This guide, part of Using Python in Excel, sets up that pipeline and explains the refresh behaviour that makes it reliable.

Prerequisites

You need Excel for Microsoft 365 with Python in Excel enabled for your plan, and permission to connect to the source from Power Query (file access, database credentials or an API key). No Python installation is involved; the libraries run in Microsoft's environment.

Step 1 — Import the source with Power Query

Choose Data → Get Data and the source type: From File → From Text/CSV for a single file, From File → From Folder for a folder of monthly exports, From Database for SQL Server and others, or From Other Sources → From Web for a URL. The Power Query editor opens with a preview. Do the cheap shaping here, before Python sees anything: remove columns you do not need, filter to the period you analyse, set data types. Power Query records each step in its own formula language, M; a typical import of a CSV looks like this in the Advanced Editor:

let
    Source = Csv.Document(File.Contents("C:\Data\sales_2026.csv"), [Delimiter=",", Encoding=65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Typed = Table.TransformColumnTypes(Promoted, {{"order_date", type date}, {"amount", type number}}),
    Recent = Table.SelectRows(Typed, each [order_date] >= #date(2026, 1, 1))
in
    Recent

You rarely need to write M by hand — each click in the editor adds a step — but reading it tells you exactly what arrives in the workbook. Name the query something meaningful, such as Sales, because that name is what Python will use.

Step 2 — Load as a table, or keep it as a connection

Close & Load To… offers two choices that matter here:

• Table puts the result on a sheet as an Excel table named after the query. People can see and filter it, and xl("Sales[#All]", headers=True) reads it.
• Only Create Connection keeps the data in the workbook's data model of queries without writing it to a sheet. A PY cell can still read it by query name.

Choose a table when people need to see the raw rows, a connection when only the analysis matters. For imports of tens of thousands of rows, connection-only keeps the workbook responsive.

Step 3 — Read the data in a PY cell

In a cell, type =PY( and reference the data. For a loaded table:

sales = xl("Sales[#All]", headers=True)
sales.dtypes

For a connection-only query, reference the query by name — no brackets, no headers argument; the query already has column names:

sales = xl("Sales")
sales.groupby("region")["amount"].sum().sort_values(ascending=False)

Either way you get a pandas DataFrame, and the rest is ordinary pandas. Types set in Power Query arrive as proper types — dates as datetimes, numbers as numbers — which saves the conversion work described in Clean Data with pandas in Python in Excel. How xl() addresses ranges, tables and names in general is covered in Reference Cells, Ranges and Tables with xl() in Python in Excel.

Step 4 — Refresh in the right order

Power Query and Python recalculate separately. Power Query fetches new data only when refreshed; Python cells recalculate from what is in the workbook. So after the source changes, the sequence is: Data → Refresh All, which re-runs the queries, and then the Python cells that depend on them recalculate with the new data.

Two settings make this dependable for a shared workbook. In the query's properties, enable Refresh data when opening the file so every reader starts from current data. And if the workbook has many Python cells, keep calculation on automatic but avoid editing during a refresh — each changed query triggers the dependent Python cells again.

Step 5 — Parameterise from a cell

Reports often need a user-chosen value — a region, a start date — applied to the external data. Put the choice in a named cell and read it in Python, filtering the imported data rather than re-querying the source:

region = xl("SelectedRegion")
start = pd.Timestamp(xl("StartDate"))
view = sales[(sales["region"] == region) & (sales["order_date"] >= start)]
view.pivot_table(index="rep", values="amount", aggfunc="sum")

Filtering in Python is right when the import is already small. When the choice should reduce what is fetched — a year of transactions instead of ten — make it a Power Query parameter instead, so the filter runs at the source and less data crosses into the workbook at all.

Step 6 — Combine a folder of files

Monthly exports dropped into one folder are a common source, and Power Query's From Folder connector combines them into one query: it lists the files, applies the same import steps to each, and appends the results with a column holding the source file name. Filter the file list in the editor — by extension, or by a name pattern such as sales_2026_* — so stray files in the folder do not break the combine step. Load the combined query as connection only and analyse it in Python:

combined = xl("MonthlySales")
per_file = combined.groupby("Source.Name")["amount"].agg(["count", "sum"])
per_file

The per-file summary doubles as a completeness check: a month with far fewer rows than the others, or a file that is missing altogether, shows up immediately. Adding next month's file to the folder and refreshing is all the maintenance the report needs. Outside Excel, the same job is done with a loop over the folder — see Combine Multiple Excel Files into One in Python.

Common pitfalls

• Trying to read files or URLs from Python. open(), pd.read_csv() on a local path and web requests fail in Python in Excel. Route every external source through Power Query.
• Forgetting to refresh. Python recalculates from stale imported data until the query is refreshed. Refresh All first, or refresh on open.
• Loading everything. A PY cell sends what it reads to the cloud on each recalculation. Filter and aggregate in Power Query so Python gets only what the analysis needs.
• Credentials for other readers. A query that works for you may prompt a colleague for credentials or be blocked by privacy levels. Test the workbook as a reader would before sharing it.

Performance and scale notes

Power Query is efficient at import and filtering, and for databases it can push filters down to the server. Python in Excel is efficient at analysis on moderate data. The combination works best when Power Query does the reduction and Python the reasoning: tens of thousands of rows reaching Python is comfortable, millions are not. When a job needs full-size data, scheduled refreshes or files on a server, a regular Python script outside Excel is the better tool — the trade-off is laid out in Python in Excel vs openpyxl and xlwings, and database loading from scripts in Moving Data Between Excel and Databases.

Conclusion

Python in Excel cannot reach files or the internet, so external data comes in through Power Query. Import and shape the source there, load it as a table when people need to see it or as a connection-only query when only Python does, read it with xl() by table or query name, and refresh the queries before relying on the Python results. Keep heavy filtering in Power Query so Python works on the rows that matter.

Frequently asked questions

Can Python in Excel read a CSV file from my computer? No. Python in Excel runs in an isolated cloud environment with no access to your file system or the internet. Import the file with Power Query first, then read the resulting table or query with xl().

Do I have to load the Power Query result onto a sheet? No. A query saved as connection only can be referenced by name from a PY cell with xl("QueryName"), which keeps large imports off the grid.

Why does my Python cell show old data after the source changed? The Python cell recalculates from what is in the workbook. Refresh the Power Query query first — Data, Refresh All — and the Python cells that depend on it recalculate afterwards.

How much data can I bring in this way? Power Query itself handles large imports, but everything a PY cell reads is sent to the cloud on each recalculation. Filter and aggregate in Power Query so Python receives the rows it needs, not the whole source.

Related

• Up one level: Using Python in Excel — how =PY() works.
• Return DataFrames and Values from Python in Excel Cells — getting results back to the grid.
• Fix Python in Excel Errors — including access errors.
• Python vs Power Query for Cleaning Excel Data — which tool for which job.