Power Query Problems

How to Fix Power Query Excel File Format Errors in SharePoint Folders

Adam Davis Oct 23, 2026 869 views

Question details

Users encounter file format errors when trying to combine Excel workbooks (XLSX) from a SharePoint folder using Power Query, despite CSV files importing correctly.

How to Fix Power Query SharePoint Folder Excel File Format Errors

Product: Microsoft Excel (Power Query)
Device & OS: not provided
Scenario: Combining multiple Excel files from a SharePoint folder into a single dataset using the Power Query Combine Files feature.
Observed behavior: The Power Query process successfully combines CSV files but throws an error when attempting to parse and combine XLSX files from the same SharePoint folder.

Before you start

Ensure you have the correct permissions to access the SharePoint document library and verify that the Excel files you are trying to combine are not corrupted or password-protected.

Solution 1Recommended

Filter Hidden Files and Apply a Custom M Function

Bypass standard Combine Files errors by filtering out unsupported items and using a custom Power Query M function to extract data directly from the binary content.

Power Query often fails when it encounters temporary or hidden files (like those starting with '~$') in a SharePoint folder.

By manually filtering the file list and invoking a custom function utilizing Excel.Workbook, you can ensure only valid Excel files are processed.

Connect to the SharePoint Folder

In Excel, go to the Data tab, click 'Get Data', select 'From File', and then 'From SharePoint Folder'. Enter your SharePoint site URL and click OK.

Filter the File List

Click 'Transform Data' to open the Power Query Editor. Filter the 'Extension' column to only include '.xlsx' files. Then, filter the 'Name' column to exclude any files beginning with a tilde (~).

Create a Custom Function

Right-click in the Queries pane and create a Blank Query. Open the Advanced Editor and paste an M function that accepts a binary parameter, calls Excel.Workbook(), and extracts data from the specific worksheet.

Invoke the Custom Function

Return to your main SharePoint query. Go to 'Add Column' > 'Invoke Custom Function'. Select your new function and apply it to the 'Content' column.

Expand the Data

Remove all columns except for the newly created custom column. Click the expand arrows at the top of the column to reveal your combined Excel data.

Filter Hidden Files and Apply a Custom M Function

Updating Sheet Names: Ensure that your custom M function references the exact worksheet name that exists across all your target Excel files.

Solution 2

Isolate the Issue with a New Excel Test File

Determine if the error is caused by a specific corrupted file in the SharePoint folder rather than a structural query issue.

Free Microsoft Office alternative

Use WPS Office for Lightweight and Seamless Spreadsheet Management

If you are frustrated by complex Power Query errors and need a streamlined, efficient way to manage and consolidate your daily spreadsheet tasks, WPS Office offers a free, highly compatible alternative to Microsoft Office.

1. Download and Install WPS Office: Visit the official WPS website to download the free suite and install it on your device.
2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx or .csv files seamlessly.
3. Consolidate Data Easily: Use the built-in Data > Consolidate feature to combine information from multiple sheets without complex query setups.

Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Lightweight and fast, avoiding heavy processing errors common in complex data queries.Built-in data consolidation tools for easy spreadsheet merging without coding.Familiar, tabbed user interface that requires zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query combine CSV files easily but fail on XLSX files?

CSV files are simple text files, making them easy for Power Query to parse sequentially. XLSX files are complex compressed archives containing multiple XML files. If Power Query encounters temporary files or unexpected structures in a SharePoint folder, it fails to parse the binary content as an Excel workbook.

How do I filter out hidden or temporary files in Power Query?

In the Power Query Editor, locate the 'Name' column. Apply a text filter to 'Does Not Begin With' and type a tilde (~) to exclude temporary files. You should also filter out hidden files using the 'Attributes' column if visible.

What does the Excel.Workbook function do in an M script?

The Excel.Workbook function reads the raw binary content of an Excel file and translates it into a structured table format. This table displays all the sheets, tables, and defined names within that workbook, allowing you to target and extract specific data.

Excel & Data Problems