Power Query Problems

Fix Power Query Cannot Combine XLSX Files from SharePoint

Jason Miller Oct 22, 2026 869 views

Question details

The user is unable to combine multiple XLSX files from a SharePoint folder using Power Query, receiving an error that the first file format is not recognized.

How to Fix Power Query Cannot Combine XLSX Files from SharePoint

Product: Microsoft Excel (Power Query)
Device & OS: not provided
Scenario: Attempting to consolidate multiple Excel workbooks (.xlsx) hosted on SharePoint using the Combine Files feature in Power Query.
Observed behavior: The combine process fails for XLSX files with a format recognition error on the first file, even though combining CSV files from the same SharePoint location works perfectly.

Before you start

Verify that you have sufficient permissions to access the SharePoint folder, and ensure that the target XLSX files are not corrupted, password-protected, or open in another application.

Solution 1Recommended

Synchronize the SharePoint Folder Locally

Bypass SharePoint cloud connection issues by syncing the folder to your local drive and connecting Power Query to the local file path.

Connecting directly to SharePoint folders via Power Query can sometimes cause parsing errors with native Excel formats. Syncing the folder to your local machine allows Power Query to process the files using local file system protocols, which is often more stable.

Sync SharePoint to Local Drive

Open your target SharePoint document library in a web browser and click the 'Sync' button in the toolbar to synchronize the folder to your local computer via OneDrive.

Connect Power Query to Local Folder

Open Excel, navigate to the Data tab, and select 'Get Data' > 'From File' > 'From Folder'.

Import and Combine

Browse to the newly synced local folder on your computer, click 'Open', and then click 'Combine' > 'Combine & Transform Data' to successfully merge the XLSX files.

Synchronize the SharePoint Folder Locally

Synchronization Complete: Any changes made to the files in SharePoint will automatically sync to your local folder, keeping your Power Query data up to date upon refresh.

Solution 2

Manually Extract Binary Content and Filter Files

Adjust the query steps to filter out unsupported files and explicitly extract the table data from the binary content before combining.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If complex cloud connectivity and Power Query errors interrupt your workflow, consider switching to WPS Office. It offers a lightweight, highly compatible environment for data analysis and consolidation, allowing you to manage your spreadsheets without the heavy overhead of complex Microsoft integrations.

1. Download and Install: Get WPS Office for free from the official website and complete the quick installation.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your XLSX files directly from your local or synced cloud folders.
3. Analyze Data Easily: Use built-in data tools to analyze, merge, and organize your spreadsheets without dealing with complex query setup errors.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats without conversion issues.Lightweight software architecture for faster installation and smoother performance on any device.Intuitive user interface that requires zero learning curve for users switching from Microsoft Office.Built-in data consolidation and worksheet merging tools that are simple to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query say the first file format is not recognized?

This typically occurs if the first file in the SharePoint folder is not a supported Excel file, is corrupted, or if Power Query encounters a hidden temporary system file (often starting with '~$'). Filtering the folder contents to only include '.xlsx' extensions usually resolves this.

Can I combine CSV and XLSX files together in a single Power Query?

Combining different file structures (like CSV and XLSX) in a single 'Combine Files' action can cause format recognition errors because Power Query uses different parsing engines for them. It is best to filter the folder by extension and combine CSV and XLSX files in separate queries.

Is there a way to automate fixing file extensions in SharePoint for Power Query?

Yes, some users set up a Microsoft Power Automate flow that monitors the SharePoint folder, automatically converts or copies files into a standardized format and extension, and saves them in a dedicated folder for Power Query to safely ingest.

Excel & Data Problems