Power Query for Data Lakes: What It Can and Can’t Do

Power Query for Data Lakes: What It Can and Can't Do

Yes—Power Query in Excel for Microsoft 365 can connect to some data-lake storage, notably Azure Data Lake Storage Gen2, and bring accessible files or data into a workbook for shaping and analysis. It is a data-preparation tool, not a general-purpose lake query engine: the source, file format, permissions, and amount of data determine what it can read efficiently.

What “Power Query for a data lake” means

A data lake is a storage environment for large collections of files and other data, often kept in their original or lightly processed form. It may hold CSV files, JSON documents, logs, images, or columnar data such as Parquet. A lake does not necessarily impose one table structure or provide a built-in way for every application to query every object.

Power Query is Microsoft’s data connection and transformation technology, available in Excel and other Microsoft products. In Excel, it can connect to supported sources, select data, apply repeatable steps such as filtering or changing data types, and load the result to a worksheet or the Excel Data Model. A saved query can usually be refreshed to retrieve updated source data, subject to access and connector behavior.

So the useful question is not simply whether Power Query supports “data lakes.” It is whether Excel’s available connector can reach the particular storage service and whether it understands the data representation you need. A folder of CSV files and a governed Delta Lake table may both live in lake storage, but they are not equivalent connection problems.

Supported connections and what they expose

In Excel for Microsoft 365 on Windows, check Data > Get Data > From Azure for an Azure Data Lake Storage connector. The exact label and availability can vary with the Excel build and organizational setup. The connector is intended for Azure Data Lake Storage Gen2. Excel also offers other source connectors, such as Azure Blob Storage, but a blob-storage connection and a Gen2 lake connection are not interchangeable in every environment.

A connector may present a file or folder listing rather than a ready-made business table. You can then choose relevant files and use Power Query transformations to combine or reshape them. For example, suppose a lake contains daily CSV exports with the same columns. A query can select the export folder, exclude files that are not part of the report, combine the matching files, and standardize date and number types. The result is a usable table, but the query still depends on the files continuing to follow a compatible structure.

Power Query connects to a supported endpoint; it does not make an unsupported storage service accessible just because that service is called a data lake. Nor does a successful connection guarantee that every format or table convention in the lake can be interpreted. If a connector is missing, confirm your Excel version, update status, and organizational policies, then check Microsoft’s current connector documentation for that product.

How a typical Excel connection works

For an Azure Data Lake Storage Gen2 source, a common workflow in Excel for Windows is:

Choose Data > Get Data > From Azure > From Azure Data Lake Storage, if that connector is available in your Excel installation.
Enter the storage account or endpoint information requested by the connector. Use the address and path supplied by the lake administrator rather than guessing a folder URL.
Sign in using an authentication method permitted by the organization. Depending on configuration, access may use an organizational account or another supported method.
Use the Navigator or Power Query Editor to select the relevant file, folder, or available data object. Preview the data before loading it.
Apply transformations, such as removing unnecessary columns, filtering rows, setting types, or combining consistently structured files.
Choose Close & Load to put the result in Excel, or use the load options available in the workbook to place it in the Data Model where appropriate.

These steps create a query definition, not a permanent copy of the lake itself. Excel loads the output you request, and a later refresh attempts to run the connection and transformations again. If the source path, credentials, schema, or permissions change, refresh may fail or return different results. The connector interface and supported sign-in choices can change; follow your organization’s instructions if they differ from a generic walkthrough.

File formats, schemas, and lakehouse tables

File format is a key distinction. CSV is relatively simple but has no strong built-in schema, so a column can be interpreted differently when values vary or new data arrives. JSON can be nested and may require expansion steps. A query that combines files generally works best when files share predictable names, columns, delimiters, encodings, and types. A new column or a changed date format can require adjustments to the transformation steps.

Columnar formats and managed lakehouse tables need more care. Parquet stores data differently from ordinary text files, and support depends on the particular Excel connector and Power Query engine available. Delta Lake tables are more than a pile of Parquet files: their transaction log defines which files and versions make up the table. Reading an arbitrary collection of underlying files is not necessarily equivalent to querying the current Delta table correctly. Use a connector or service that explicitly understands the table format, or ask the data platform team for a supported SQL endpoint or curated export.

This is a common misconception: storing a dataset in a lake does not automatically turn it into a database table that Excel can browse. A lakehouse platform may add cataloging, table management, and SQL access, but those services are separate from the raw storage layer. Power Query can use an appropriate connector or endpoint when one is available; it does not supply the missing catalog, compute engine, or table-format rules by itself.

Performance and practical size limits

Power Query can be convenient for departmental extracts and moderate analytical datasets, but Excel is not designed to load an entire enterprise lake. A worksheet can contain at most 1,048,576 rows and 16,384 columns. The Data Model has different capacity characteristics, but it is still constrained by available memory, workbook size, machine resources, and the way data is loaded. Large refreshes can be slow or fail even when the source itself can store far more data.

Where possible, reduce data before it reaches the workbook. Ask for a curated table or a view that filters by date, business unit, or relevant categories. Select only necessary columns and avoid loading intermediate queries that are used only to produce another query. For example, a monthly sales report usually does not need every event row from several years if the source can provide monthly totals or the required date range.

Query folding is another consideration. With some connectors, Power Query can translate supported transformation steps into operations performed by the source, reducing how much data must be transferred. Folding depends on the connector and the steps used; it is not guaranteed for every file-based lake connection. For files, Excel may have to retrieve and process substantial data locally. Previewing a query is not proof that a full refresh will be fast, so test with representative volumes and confirm where processing occurs.

Permissions, credentials, and safe handling

Access to lake data is controlled by the storage platform and the identity used to connect. In Azure environments, authorization may involve account-level roles, filesystem or directory permissions, or organization-managed identity policies. Having a URL does not mean a user is authorized to read its contents. If connection succeeds but a folder or file is unavailable, ask the administrator to check both the endpoint and the relevant permissions.

Use the authentication method approved by your organization and avoid placing access keys or other secrets in workbook cells, query text, or shared documents. Excel stores connection and credential information according to its data-source privacy and credential settings, but the workbook itself may still expose query logic, paths, or loaded data when shared. Apply your organization’s classification and sharing rules, and do not distribute a workbook containing information its recipients are not allowed to see.

Privacy levels and organizational policies can also affect how Power Query combines sources. A query that merges a lake source with another data source may be restricted or processed differently to prevent unintended data leakage. If a refresh warning appears, do not bypass it without understanding which sources are being combined and what information may flow between them.

Excel Power Query versus Power BI and SQL

Excel and Power BI share Power Query technology, but their connector lists, deployment options, refresh behavior, and capacity are not identical. A connector documented for Power BI is not automatically available in Excel in the same way. Power BI is often a better destination for governed reports that need shared models, scheduled refresh, or larger-scale service deployment. The appropriate connector and licensing depend on the platform and organization.

A SQL endpoint or warehouse can be a better interface when users need to filter or aggregate very large datasets before returning results. In that design, the lake platform or query service performs the heavy work, and Power Query retrieves a smaller result through a supported database connector. This may improve responsiveness and governance, but it requires a configured endpoint, appropriate permissions, and sometimes help from a data engineer or administrator.

For an individual analyst exploring a few well-structured files, Excel Power Query can be simpler and more transparent. For recurring reports used by a team, a centrally managed semantic model, Power BI dataset, or governed SQL view may reduce duplicated logic and inconsistent copies. The right choice depends on data volume, refresh needs, audience, governance, and whether the source format has a supported connector.

Common connection problems

Several problems have straightforward causes. A connector may not appear because the Excel build differs, the feature is managed by an administrator, or the user is following instructions for another Microsoft product. A sign-in loop or access-denied message usually calls for checking the account and permissions with the lake owner. A query that once worked may break after a file is renamed, moved, or changed structurally.

When troubleshooting, record the connector name, endpoint, error text, and whether the problem occurs during sign-in, navigation, preview, or refresh. Check whether the source is reachable in the approved network environment, and verify that the account has access to the exact filesystem and directory. If combining files, inspect a newly added file for changed headers, types, or encoding. These details help distinguish an authentication issue from a query-step or format issue.

No connector: Verify the Excel product and update channel, then consult current Microsoft connector documentation.
Access denied: Ask the data owner to validate identity, role assignments, and directory or filesystem permissions.
Refresh error after a schema change: Review the first affected transformation step and compare the new file with the expected columns.
Slow refresh: Reduce the requested data, use a source-side view or endpoint, and check whether transformations can be performed by the source.

Frequently Asked Questions

Can Excel Power Query connect directly to a data lake?
It can connect directly to certain supported lake-storage sources, including Azure Data Lake Storage Gen2 when the connector is available. “Data lake” is not one universal connector, so the exact service and data format matter.

Can Power Query read Delta Lake tables?
Do not assume that a generic connection to the underlying files reads a Delta table correctly. Delta uses a transaction log as well as data files. Use a connector or query endpoint that explicitly supports Delta table semantics.

Can I use Power Query to query the whole lake?
Power Query can retrieve and transform data it can access, but Excel is not a lake-scale distributed query engine. Filter and aggregate at the source where possible, then load a manageable result.

Why does my Azure Data Lake connection say access denied?
The signed-in identity may lack authorization for the account, filesystem, or directory, or your organization may require a particular authentication route. Ask the lake administrator to check the endpoint and permissions for your account.

Is Power Query in Excel the same as Power Query in Power BI?
They share the Power Query technology, but connectors and refresh or deployment capabilities can differ. Confirm that a connector is supported in Excel specifically before designing a workflow around it.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.