How to Access SharePoint Subfolders Dynamically in Power Query
Question details
The user needs to dynamically connect to files within different SharePoint or OneDrive subfolders using a parameter 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.
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.
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.
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.
Click New Source > More > SharePoint Folder. Enter your SharePoint root URL. When the preview window opens, click 'Transform Data'.
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.
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.
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.

Filter SharePoint.Files Output using a Parameter
Extract all files from the SharePoint site and filter the results down to the specific subfolder path defined by your parameter.
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. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Workbooks: Directly open your existing Excel workbooks; WPS ensures high compatibility with standard formatting and formulas.
- 3. Analyze Data Effectively: Utilize powerful built-in tools like PivotTables, advanced filtering, and chart generation to process your data without complex backend queries.

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.




