Power Query Problems

How to Access SharePoint Subfolders Dynamically in Power Query

Adam DavisAdam Davis Nov 3, 2026 869 views

Question details

The user needs to dynamically connect to files within different SharePoint or OneDrive subfolders using a parameter in Power Query.

How to Access SharePoint Subfolders Dynamically in Power Query
Product
Excel Power Query
Device & OS
not provided
Scenario
Trying to dynamically switch data sources between different subfolders containing identically structured files using a parameter list.
Observed behavior
Changing the parameter value does not currently update the query results, as the folder path is not properly evaluating dynamically.
Before you start

Ensure you have the exact URL to your SharePoint site or OneDrive directory, and verify that your Microsoft account has sufficient read permissions for all the target subfolders.

Solution 1Recommended

Use SharePoint.Contents with a Dynamic Parameter

Create a parameter to hold your folder path and use the SharePoint.Contents function to dynamically navigate your file list.

SharePoint.Contents is generally more efficient than SharePoint.Files for this scenario because it lets you navigate through specific folders without loading the entire site's metadata first.

1
Create a Text Parameter

In the Power Query Editor, go to Home > Manage Parameters > New Parameter. Name it 'TargetFolder' and set its current value to the exact name of your default subfolder.

2
Connect to SharePoint

Click New Source > More > SharePoint Folder. Enter your SharePoint root URL. When the preview window opens, click 'Transform Data'.

3
Modify the M Code to Use SharePoint.Contents

In the formula bar, replace 'SharePoint.Files' with 'SharePoint.Contents'. This will change the view from a flat file list to a hierarchical folder view.

4
Apply the Parameter to the Path

Navigate to the column containing your folder names. Apply a text filter to this column. Then, open the Advanced Editor and replace the hardcoded folder string in the filtering step with your 'TargetFolder' parameter.

5
Refresh and Verify

Change the parameter value from the main Power Query window to a different subfolder and click 'Refresh Preview' to ensure the dataset updates based on the parameter.

Use SharePoint.Contents with a Dynamic Parameter
Check Parameter Case Sensitivity: Power Query is case-sensitive. Ensure the text values passed into your parameter exactly match the folder names in SharePoint.
Free Microsoft Office alternative

Need a Faster Way to Process Spreadsheet Data?

While connecting to dynamic SharePoint subfolders requires complex Power Query setups in Microsoft Excel, WPS Office provides a lightweight, highly compatible, and free alternative for your daily spreadsheet and data analysis needs. Enjoy seamless format compatibility without the heavy resource footprint.

  1. 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
  2. 2. Open Your Workbooks: Directly open your existing Excel workbooks; WPS ensures high compatibility with standard formatting and formulas.
  3. 3. Analyze Data Effectively: Utilize powerful built-in tools like PivotTables, advanced filtering, and chart generation to process your data without complex backend queries.
Fully compatible with Microsoft Excel (.xlsx, .xls) formatsLightweight application that opens large datasets instantlyFree and easy-to-use interface with familiar formulas and pivot tablesBuilt-in cloud syncing for smooth cross-device collaboration
microsoft office alternative - wps office

Frequently Asked Questions

Why do my Power Query results remain the same after changing the parameter?

This usually happens if the parameter is not properly linked in the query steps (e.g., hardcoded paths remain in the Advanced Editor), or if the preview cache hasn't been refreshed. Check the M code in the Advanced Editor to ensure the parameter name replaces the static text string entirely.

What is the difference between SharePoint.Files and SharePoint.Contents?

SharePoint.Files returns a flat list of all files across the entire SharePoint site and all of its subfolders. SharePoint.Contents returns a hierarchical view, allowing you to click and navigate down through specific folders, which is often much faster when dealing with large sites.

How do I fix authentication errors when connecting to SharePoint in Power Query?

Go to Data > Get Data > Data Source Settings in your spreadsheet. Locate your SharePoint URL, click 'Clear Permissions', and then try reconnecting. When prompted, select 'Organizational Account' (not Windows or Anonymous) and sign in with your Microsoft 365 credentials.