Excel Power Query Cannot Combine Files From SharePoint: Fix

When you try to combine multiple files from a SharePoint folder using Power Query in Excel, the operation may fail with errors such as ‘Access to the resource is forbidden’ or ‘We couldn’t authenticate with SharePoint.’ This problem typically occurs because Power Query cannot establish a trusted connection to the SharePoint site due to incorrect authentication settings, missing permissions, or a mismatch in how the folder path is provided. This article explains the root causes behind this failure and provides a clear set of steps to successfully combine files from SharePoint using Power Query.

Why Power Query Fails to Combine Files From SharePoint

Power Query uses the SharePoint REST API or the legacy SharePoint web service to read file lists and download file contents. When you connect to a SharePoint folder using a URL like https://contoso.sharepoint.com/sites/marketing/Shared%20Documents/FolderA, Power Query must authenticate with the site and then enumerate the files. The combine operation fails if any of these conditions are true:

• The SharePoint site requires modern authentication (OAuth 2.0) and Power Query is trying legacy authentication.
• The user account does not have ‘Read’ or ‘Contribute’ permissions on the site or the specific folder.
• The folder contains unsupported file types (such as .exe or .zip) that Power Query cannot read.
• The privacy level of the SharePoint data source is set to ‘Private,’ blocking the combine step that merges data from multiple files.
• The URL includes a document library name that is not a valid SharePoint list.

The most common root cause is that users try to connect to a SharePoint folder URL directly in the ‘From Folder’ connector, which expects a local or network path, not a SharePoint web URL. Power Query’s ‘From SharePoint List’ connector is designed to work with SharePoint sites and document libraries, making it the correct starting point for combining files.

Steps to Connect to SharePoint and Combine Files

Follow these steps to connect to a SharePoint document library and combine all Excel files from a specific folder. These steps assume you are using Excel for Microsoft 365 or Excel 2019 or later.

1. Open a blank workbook in Excel
   Start with a new workbook. Go to the Data tab and click Get Data > From Other Sources > From SharePoint List.
2. Enter the SharePoint site URL
   In the dialog box, enter the root URL of your SharePoint site, for example, https://contoso.sharepoint.com/sites/marketing. Do not include the document library or folder path. Click OK.
3. Sign in with your Microsoft 365 account
   If prompted, select Microsoft account or Organizational account and sign in with the credentials that have access to the SharePoint site. Check the box Use my current credentials if you are already signed in to Excel. Click Connect.
4. Select the document library
   After authentication, the Navigator window shows a list of SharePoint lists and libraries. Find the document library that contains your files, for example, Shared Documents. Select it and click Transform Data to open the Power Query Editor.
5. Filter to the specific folder
   In the Power Query Editor, you will see a table with columns like Name, Folder Path, and Content. Click the filter icon on the Folder Path column and choose Text Filters > Contains. Enter the folder name, for example, FolderA, and click OK.
6. Filter to the file type you need
   Click the filter icon on the Name column and choose Text Filters > Ends With. Enter .xlsx or the extension of your files. Click OK.
7. Combine the files
   Select the Content column. On the Home tab, click Combine Files. In the dialog that appears, Power Query will sample one file to determine the structure. Choose the parameter you want to use for combining, such as Sheet1, and click OK.
8. Load the combined data
   After the combine operation completes, click Close & Load on the Home tab. Excel will load the combined data into a new worksheet.

If Power Query Still Cannot Combine Files From SharePoint

Even after following the correct connection method, you may still encounter errors. Below are the most common failure patterns and their specific fixes.

Error: ‘Access to the resource is forbidden’

This error means Power Query does not have permission to read the files. Verify that your Microsoft 365 account has at least Read permissions on the SharePoint site and the document library. If you are using a guest account, the site administrator must grant explicit access to the library. To test, open the SharePoint site in a browser and confirm you can view the files manually.

Error: ‘We couldn’t authenticate with SharePoint’

This error usually occurs because the authentication token has expired or because Power Query is using cached credentials. Go to File > Options > Current Workbook > Privacy Options and set the privacy level for SharePoint data sources to None. Then, in Power Query, go to Data Source Settings (under the Home tab in the Query Editor), select the SharePoint entry, and click Change Credentials. Sign in again with your Microsoft 365 account.

Power Query shows only folder names, not file contents

This happens when the SharePoint list connector returns the folder structure but not the file data. The issue is that the folder path contains special characters or spaces that are not properly encoded. In the Power Query Editor, use the Transform tab and select Replace Values to replace %20 with a space, or use the Extract function to parse the correct folder name from the Folder Path column. Then apply the filter again.

Combine operation fails with ‘DataFormat.Error’

This error indicates that one or more files in the folder are corrupted or not valid Excel files. Before combining, filter the Name column to exclude any files that are not .xlsx, .xlsb, or .csv. If you must include older .xls files, convert them to .xlsx using the SharePoint web interface or a desktop converter before running Power Query.

From Folder Connector vs From SharePoint List Connector: Key Differences

Item	From Folder Connector	From SharePoint List Connector
Connection method	Uses file system path (local or network)	Uses SharePoint REST API
Authentication	Windows authentication or anonymous	OAuth 2.0 with Microsoft 365 account
Supports SharePoint folders	No, fails with web URL	Yes, lists all files and folders
Privacy level required	None or Organizational	None or Organizational
Combine files step	Not available directly	Built-in on Content column

You can now connect to any SharePoint document library and combine multiple Excel files into a single table using Power Query. After the initial setup, refresh the query by right-clicking the query in the Queries & Connections pane and selecting Refresh. For large folders with hundreds of files, consider using the Combine Files dialog’s Sample File parameter to specify the exact sheet and headers, which prevents errors from inconsistent file structures.