Power Query Problems

Fix SharePoint.Files Power Query 500 Error for Binary Files

Natalie Taylor Oct 22, 2026 868 views

Question details

Users encounter a 500 Internal Server Error when trying to open binary files extracted via the SharePoint.Files function in Power Query.

How to Fix SharePoint.Files Power Query 500 Errors When Opening Binary Files

Product: Power Query
Device & OS: not provided
Scenario: Connecting to a SharePoint site to import data, successfully listing the files, but failing to open the actual file contents.
Observed behavior: Power Query successfully lists the files, but selecting or clicking on a 'Binary' value in the Content column returns a '500 Internal Server Error'.

Before you start

Verify that you have the correct SharePoint site URL and ensure you possess at least read permissions for the specific files you are trying to access.

Solution 1Recommended

Specify the SharePoint API Version in the Connection String

Adding the API version parameter to the SharePoint.Files function resolves the 500 error by forcing Power Query to use a stable endpoint for fetching binary content.

The 500 Internal Server Error often occurs because Power Query defaults to an API version that might conflict with how your specific SharePoint environment is configured to deliver file contents. By explicitly defining the API version as 15 in the M-code, you bypass this routing issue.

Open the Power Query Editor

Navigate to your Excel or Power BI workbook, go to the 'Data' tab, and open the Power Query Editor for the query that is generating the error.

Access the Advanced Editor or Source Step

Click on the 'Advanced Editor' button on the Home tab, or select the 'Source' step in the 'Applied Steps' pane on the right side of the window.

Modify the SharePoint.Files Function

Locate the connection string and add the ApiVersion record parameter. Change it to look like this: SharePoint.Files("https://yoursite.sharepoint.com/sites/example/", [ApiVersion=15]). Make sure to replace the placeholder URL with your actual SharePoint site address.

Apply Changes and Expand the Binary

Click 'Done' or press Enter to apply the updated M-code. You can now click on the 'Binary' link in the Content column to extract your file without triggering the 500 error.

Specify the SharePoint API Version in the Connection String

Why ApiVersion=15?: ApiVersion=15 explicitly directs Power Query to use the SharePoint REST API v15, which is highly reliable for querying directory lists and downloading binary files simultaneously.

Free Microsoft Office alternative

Experience Faster Data Handling with WPS Office

If you frequently encounter complex connection errors like the Power Query 500 issue in Microsoft Office, consider trying WPS Office. It provides a lightweight, highly compatible spreadsheet environment that makes everyday data management straightforward and hassle-free.

1. Visit the WPS Website: Go to the official WPS Office website to find the latest version for your operating system.
2. Download the Installer: Click the 'Free Download' button to get the lightweight installation package.
3. Install and Open: Run the installer, open WPS Spreadsheets, and seamlessly continue working on your existing Excel files.

100% compatible with Microsoft Excel formats (.xlsx, .csv, .xls)Lightweight installation with incredibly fast startup timesClean, intuitive interface that requires no learning curveFree to download and use for all essential data processing tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query list SharePoint files but fail to open the binary data?

This happens due to API routing discrepancies. While the endpoint for fetching the directory list works on the default setting, extracting the actual binary data requires specific API handling which can crash and return a 500 error if an API version isn't explicitly declared.

What does [ApiVersion=15] do in Power Query?

It is an optional record parameter in the SharePoint.Files function that forces Power Query to utilize the SharePoint REST API v15. This specific version is generally more stable and correctly handles requests for file contents.

Can I apply this same fix to the SharePoint.Contents function?

Yes, the [ApiVersion=15] parameter can also be appended to the SharePoint.Contents function if you are navigating folders instead of a flat file list and experience similar connectivity or extraction errors.

Excel & Data Problems