Power Query Problems

Fix Power Query Format Not Recognized Error in SharePoint Files

Rana Garcia Oct 7, 2026 868 views

Question details

The user encounters a 'format not recognized' error when attempting to combine Excel Binary Workbooks (XLSB) from a connected SharePoint folder using Power Query.

How to Fix Power Query Format Not Recognized Error When Combining SharePoint Files

Product: Microsoft Excel
Device & OS: not provided
Scenario: Combining multiple Excel files directly from a SharePoint directory using the Power Query 'Combine Files' feature.
Observed behavior: Power Query displays a 'format not recognized' error, typically caused by the tool attempting to parse folders, temporary hidden files, or unsupported file extensions present in the SharePoint directory.

Before you start

Verify that you have read access to the SharePoint folder and ensure the target Excel Binary Workbook (XLSB) files can be opened normally in a web browser or desktop app without corruption.

Solution 1Recommended

Filter Out Unsupported Files and Folders in Power Query

Prevent Power Query from attempting to combine temporary files, non-Excel formats, and folders by applying strict text filters before expanding the binary content.

When connecting to a SharePoint folder, Power Query retrieves all items in that directory, including subfolders, non-Excel files, and hidden temporary files (often created when a file is open). If you click 'Combine' immediately, Power Query tries to merge everything, triggering a format error when it hits an invalid item.

Open Power Query Editor

Connect to your SharePoint folder using 'Get Data' > 'From File' > 'From SharePoint Folder'. Instead of clicking 'Combine', click the 'Transform Data' button to open the Power Query Editor.

Filter file extensions

Locate the 'Extension' column. Click the filter dropdown arrow in the column header and select only '.xlsb' (or '.xlsx' depending on your target files) to exclude unsupported formats.

Remove hidden temporary files

Find the 'Name' column, click the filter dropdown, and choose 'Text Filters' > 'Does Not Begin With'. Enter '~$' in the dialog box and click 'OK' to exclude hidden temporary files that cannot be combined.

Combine the filtered files

Once the list only contains valid Excel files, click the 'Combine Files' icon (the two downward arrows) located on the header of the 'Content' column to merge the data securely.

Filter Out Unsupported Files and Folders in Power Query

Best Practice: Applying filters before combining not only prevents format errors but also significantly speeds up the query refresh time by reducing the amount of data processed.

Solution 2

Check for File Corruption or Sync Issues

Sometimes, an incomplete SharePoint sync or a temporary network glitch can cause a valid file to appear corrupted or unrecognized to Power Query.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Data Handling

If you frequently deal with complex Excel files, XLSB formats, or experience heavy processing issues, WPS Office Spreadsheet provides a lightweight, highly compatible, and free alternative to Microsoft Excel.

Fully compatible with Microsoft Excel file formats including XLSX, XLS, and XLSB.Lightweight software architecture ensures fast opening and smooth scrolling of large datasets.Familiar user interface ensures zero learning curve for users migrating from Microsoft Office.Built-in advanced data analysis tools without the need to install extra add-ins.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query show 'format not recognized' for perfectly fine Excel files?

This usually happens because Power Query accidentally tries to parse an invisible temporary file (which starts with ~$), a subfolder, or a system file, treating it as an Excel document. Filtering these out before combining resolves the issue.

Does Power Query support combining XLSB (Excel Binary) files?

Yes, Power Query natively supports combining XLSB files. However, you must ensure that only XLSB files are passed into the Combine Files step by explicitly filtering the 'Extension' column in the Power Query Editor.

How do I view hidden temporary files in the SharePoint Power Query preview?

Temporary files automatically appear in the 'Name' column of the Power Query preview with a '~$' prefix. They might not be visible in your local synced folder, but Power Query detects them when pulling data directly from SharePoint.

Excel & Data Problems