Excel Power Query SharePoint Folder: Connect and Refresh

How-to basics · Updated November 21, 2026

Yes—Excel for Microsoft 365 on Windows can use Power Query to connect to a SharePoint document library: choose Data > Get Data > From File > From SharePoint Folder, enter the SharePoint site URL, then filter, combine, and load the files you need. Enter the site address, such as https://contoso.sharepoint.com/sites/Operations, rather than a copied link to a particular folder or file.

What the SharePoint Folder connector does

The SharePoint Folder connector gives Power Query a list of files it can access in a SharePoint site and its folders. The list typically includes a file name, extension, modified date, folder path, and a Content value representing the file itself. You can filter that list, combine compatible files, and transform the resulting data before loading it into Excel.

This is useful when a team regularly adds files with a consistent layout—for example, monthly sales workbooks saved in a shared library. Instead of opening and copying each workbook by hand, you can build one query and refresh it to retrieve the current set of matching files. The connector is not limited to Excel files; it can return other file types too, although Power Query can combine only formats and structures it knows how to interpret.

A SharePoint document library is different from a SharePoint list. Use the folder connector for documents such as workbooks and CSV files. If the information is stored as list records in SharePoint, use a SharePoint list connector instead. A SharePoint site is the starting point for either connection, but the connectors return different kinds of data.

Before you connect

Make sure you can open the site and library in a browser using the same work or school account you plan to use in Excel. Your SharePoint permissions determine which files Power Query can retrieve; the connector does not bypass library access rules. If your organization requires a particular sign-in method or blocks desktop connections, an administrator may need to check the tenant’s settings.

  • Copy the site address from the browser, stopping at the site name. For example, use https://contoso.sharepoint.com/sites/Operations, not a long address containing Forms/AllItems.aspx, a file name, or a sharing token.
  • Know which library and subfolder contain the files you want. The site URL can return files from multiple folders, so you will normally narrow the results in Power Query.
  • Check that the files share a usable structure if you intend to combine them. Consistent column headings, worksheet or table names, and data types make the result much easier to maintain.
  • Use Excel for Microsoft 365 on Windows for the steps below. Power Query and connector availability can differ on Mac, and Excel for the web does not provide the same full authoring experience as desktop Excel.

Connect Excel to the SharePoint site

  1. In Excel for Microsoft 365 on Windows, open a workbook and select Data > Get Data > From File > From SharePoint Folder. If the command is not visible, check the Get Data menu rather than searching for a folder picker in the SharePoint website.
  2. Paste the site URL into the SharePoint Folder dialog and select OK. Use the site’s address, not the address of a library view or an individual document.
  3. If prompted, choose Organizational account and sign in with the account that has access. Select the appropriate authentication level when Excel asks which URL the credentials apply to. If Excel already has an incorrect saved sign-in, use its data-source settings to clear or update the permissions for that source.
  4. When the file preview appears, choose Transform Data to inspect and filter the results before loading. If you already know the files have the same layout and want Power Query to combine them, choose Combine or Combine & Transform Data when that option is offered.

The first connection may take time if the site contains many files or has a large folder structure. Let the preview finish before deciding that it has stopped responding. The query stores the connection and transformation steps in the workbook; it does not normally store a permanent, independent copy of every source file as part of the query definition.

Filter to the right folder and files

Filtering is important because a site can contain many libraries and subfolders. In the Power Query Editor, inspect the Folder Path column and filter it to the specific path you need. Also filter Extension, Name, or another suitable metadata column to exclude unrelated file types. For example, a query for monthly reporting workbooks might keep only the intended reports folder and files with the .xlsx extension.

Filter before combining. That reduces the number of files Power Query has to process and helps avoid combining a similarly named workbook from a different team folder. Filtering only on a file name is often not enough: two departments may both have a file named Summary.xlsx. The folder path distinguishes them.

You may also see temporary or unrelated files, including Excel lock files whose names begin with ~$. Exclude those if they appear in the results. A filter based on a folder path should be checked after a folder is renamed or moved; a saved query can otherwise return no files even though the documents still exist elsewhere on the site.

Combine files with a consistent layout

Power Query can combine files when they have a compatible structure. Select the combine option for the Content column or use the Combine control shown in the file preview. Excel asks Power Query to inspect a sample file and identify the worksheet, table, or other object to extract. It then creates a main query and supporting queries or transformation steps that apply the chosen extraction to the other matching files.

For example, suppose a library contains one workbook per month, each with an Excel table named SalesData and columns for Date, Region, and Amount. Combining those files can append the rows into one query result. If one file instead has different column headings, a renamed table, or an extra title row, it may produce missing values or errors. Before relying on a combined result, inspect the sample transformation and check that the output includes the expected columns and row counts.

Combining is not the same as merging. Combine appends data from multiple files that follow a similar pattern. Merge joins two tables using matching columns, such as a customer ID. If you need to add a lookup table to the combined result, first combine the files and then use Power Query’s Merge Queries feature with the appropriate key.

Clean and load the query result

In the Power Query Editor, review the applied steps and data types. Remove columns you do not need, rename columns for clarity, and filter out blank rows or headings that were accidentally included as data. Set dates and numbers to suitable types rather than assuming that a value which looks numeric will always be interpreted correctly. Regional date formats and decimal separators can affect how text is converted.

When the preview is ready, select Home > Close & Load to load the result to a worksheet. Use the arrow beside Close & Load or the load options when you want to choose a different destination or load the result to the Data Model. Loading to a worksheet is convenient for ordinary reporting, but Excel worksheets have a limit of 1,048,576 rows. For a result too large for a sheet, consider loading it to the Data Model or reducing it in Power Query.

After loading, the result is an output of the query, not a live view that changes the moment someone edits a file in SharePoint. It reflects the source files as of the last successful refresh. Avoid typing corrections directly into the query output table: a refresh can replace those cells. Make corrections in the source files or as deliberate Power Query steps instead.

Refresh, share, and manage access

To retrieve current files, select Data > Refresh All. You can also right-click the query result or use the Queries & Connections pane to refresh a particular query. For refresh settings, open Data > Queries & Connections, right-click the query, and choose Properties. Depending on the connection and Excel build, the properties can include options such as refreshing when the workbook opens.

Refreshing requires valid credentials and access to the source. Sending someone the workbook does not grant that person permission to the SharePoint site. A colleague who opens it may need to sign in with their own account and have permission to the library. This is also why a query that refreshes successfully for its creator can fail for another user.

Do not assume that every refresh option works identically in Excel for the web, Excel for Mac, or an automated service. Supported connectors and refresh behavior vary by platform and environment. If a workbook is stored online and needs scheduled refresh, confirm that the specific connection is supported by the service you plan to use; desktop Excel’s Refresh All is not itself a scheduled cloud refresh service.

Common connection and refresh problems

  • Excel rejects the URL: Try the site root, such as https://contoso.sharepoint.com/sites/Operations. Remove document names, sharing links, and library-view query strings from the address.
  • Access is denied or sign-in repeats: Confirm the account can open the site in a browser. In Excel, check the saved permissions for the SharePoint data source and sign in with the correct Organizational account. Your administrator may need to investigate a tenant policy or permission issue.
  • The preview contains too many files: Connect at the site level, then filter Folder Path and Extension in Power Query before combining. Do not rely on file names alone to identify the correct folder.
  • Some files fail during combine: Check for inconsistent worksheets, table names, headers, or file formats. Open the generated sample-file transformation and inspect the individual source files that do not match the expected pattern.
  • Refresh is slow or times out: Reduce the set of files early with metadata filters, remove unused columns, and avoid loading data that the report does not need. Large files, many files, network conditions, and SharePoint service limits can all affect refresh time.
  • A renamed folder returns no rows: Review the Folder Path filter and update it to the current location. The query may still be valid while its saved filter points to the old path.

SharePoint Folder versus other approaches

Use the SharePoint Folder connector when your source data is stored in files and you want a repeatable process to read one or many of them. Use a SharePoint list connector when the records live in a list rather than in documents. These connectors are not interchangeable: a list connector returns list fields, while the folder connector returns file metadata and file contents.

Syncing a library with the OneDrive sync app is another option, but it is not the same connection method. A query built from a local synced folder may depend on that folder path being available on the computer running the refresh. The SharePoint Folder connector connects to the online site instead, which is generally more appropriate when a workbook should refer to the shared source rather than a particular user’s local file path.

For a one-time import, manually downloading files may be adequate. For repeated reporting, Power Query is more reproducible because its filters and cleanup steps are saved. For a large or operational reporting pipeline, a different Microsoft service or database may be more suitable; the right choice depends on volume, refresh requirements, permissions, and what tools your organization licenses and supports.

Frequently Asked Questions

Can Power Query connect to a specific SharePoint subfolder?
Connect using the SharePoint site URL, then filter the Folder Path column to the subfolder. This is usually more reliable than pasting a deep link copied from the browser.

Can I combine Excel files from SharePoint automatically?
Yes, if the files have a compatible structure. Use the combine option, select the appropriate sample worksheet or table, and check that headings and data types are consistent across the files.

Does refreshing the workbook give everyone access to the files?
No. Each person still needs SharePoint permission and may need to authenticate. Workbook access and library access are separate permissions.

Can I use the SharePoint Folder connector in Excel for Mac?
Connector availability and Power Query features differ on Mac. If the SharePoint Folder command is unavailable in your Excel build, use a supported Windows desktop connection or another method approved by your organization.

Why does my query show files from other folders?
The connector can return files across the connected site’s folder structure. Filter Folder Path to the precise library location and check the filter again if files are moved or folders are renamed.

Related

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.