Power Query Problems

How to Fix Power Query Showing Only One File from a SharePoint Folder

Algirdas Jasaitis Oct 21, 2026 868 views

Question details

Users are unable to view the complete list of files when importing data from a SharePoint folder using Power Query.

How to Fix Power Query Showing Only One File from a SharePoint Folder

Product: Excel Power Query
Device & OS: not provided
Scenario: Connecting to a shared SharePoint folder to consolidate or import multiple files into a single dataset.
Observed behavior: Power Query displays only a single file instead of the entire folder directory, or returns a 500 Internal Server Error when a specific file is selected.

Before you start

Verify that your SharePoint folder URL is correct and ensure you have sufficient permissions to view and access all files within the target directory.

Solution 1Recommended

Change the SharePoint Connector API Version

Adjusting the API version from the default setting to 15 or Auto resolves file list display issues, especially on non-English SharePoint sites.

By default, Power Query uses API version 14 if no specific version is declared. Non-English SharePoint sites and newer configurations require at least API version 15 to accurately read directory contents. Upgrading this parameter forces Power Query to use the correct protocol.

Open Advanced Editor

In the Power Query Editor, go to the Home tab and click on 'Advanced Editor' to view your query's M code.

Locate the Source Step

Find the step where the connection to SharePoint is established, typically starting with SharePoint.Files or SharePoint.Contents.

Modify the API Version

Add or modify the ApiVersion parameter within the function arguments to state [ApiVersion = 15] or [ApiVersion = "Auto"].

Apply and Close

Click 'Done' to close the Advanced Editor, then refresh the preview to confirm the full list of files is now visible.

Change the SharePoint Connector API Version

API Compatibility: Setting the API version to Auto allows Power Query to dynamically negotiate the best version with your specific SharePoint server setup.

Solution 2

Filter Results by File Type

Filtering the initial query by file extension can bypass folder display limits and isolate the exact data files needed.

Solution 3

Sync Folder Locally via OneDrive

Syncing the SharePoint folder locally bypasses the online connector entirely, providing an alternative data import path if server issues persist.

Free Microsoft Office alternative

Need a simpler way to manage your data? Try WPS Office

If complex Power Query configurations, SharePoint API settings, and server errors are slowing down your productivity, consider making the switch to WPS Office. It provides robust spreadsheet capabilities, a highly intuitive interface, and seamless performance for your daily data management tasks without the enterprise-level setup hurdles.

1. Download WPS Office: Visit the official WPS website and download the free software suite.
2. Install the Application: Run the lightweight installer and follow the quick setup instructions.
3. Open Your Data Files: Launch WPS Spreadsheet and instantly open your existing Excel workbooks and CSV files with perfect compatibility.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Lightweight installation with incredibly fast loading times.Familiar, easy-to-use interface that requires no steep learning curve.Completely free suite including Word, Excel, PowerPoint, and PDF tools.

microsoft office alternative - wps office

Frequently Asked Questions

What is the default API version for Power Query SharePoint connections?

If the API version is not explicitly declared in the Power Query Advanced Editor, the connector defaults to using API version 14.

Why does Power Query require API version 15 for non-English SharePoint sites?

Non-English SharePoint sites use updated directory structures and metadata handling that are only supported by API version 15 or higher. Using the default version 14 on these sites often results in incomplete or single-file folder lists.

Why do I get a 500 Internal Server Error when selecting a SharePoint file in Power Query?

A 500 Internal Server Error typically points to a server-side issue, such as restrictive permissions, misconfigured site settings, or corrupted metadata. You should contact your SharePoint administrator to check the server logs and confirm your account has the correct access rights.

Is it safe to use a locally synced folder for Power Query instead of SharePoint?

Yes, it is safe for personal use. However, if you are sharing the Excel file with colleagues, using a locally synced folder is not recommended. The file path relies on your specific Windows username, meaning the query will fail when other users attempt to refresh the data.

Excel & Data Problems