Guidance Required: Load Multiple Subfolders as Separate Tables in Power Query

[SinghVikas27]

I have a parent SharePoint folder containing multiple subfolders. Each subfolder has different file types/structures, and I want to load each subfolder files as a separate dataset in Power Query instead of combining them into one query.

Is there a dynamic way to connect to the parent folder only once and then split the data so that each subfolder becomes its own query/table in Power BI, without manually connecting  to each subfolder or duplicating it  one by one?

[v-agajavelly]

Hi @SinghVikas27 ,

I hope the guidance shared was useful in getting this sorted. If everything is resolved, that’s great but if you still run into any questions, feel free to let us know and we’ll be glad to help further.

Regards,
Akhil.

[v-agajavelly]

Hi @SinghVikas27 ,

Circling back were you able to test the dynamic setup with new subfolders yet? If it’s still not picking them up automatically, could you share a quick example of your folder structure? That’ll help narrow down whether we need to tweak the query or add a step to handle edge cases.

Regards,
Akhil.

[v-agajavelly]

Hi @SinghVikas27 ,

Just checking back in. Did you get a chance to try the solution I shared above?
The idea was to make the setup fully dynamic, so whenever a new subfolder appears it’s automatically picked up on refresh. That way, you don’t need to drill down manually each time a new folder shows up. Would be great to hear if this worked in your scenario, or if you’re still running into challenges.
Happy to help fine-tune further if needed.

Regards,
Akhil

[v-agajavelly · Accepted solution]

Hi @SinghVikas27 ,

Thanks for raising this! The steps the super user shared will definitely help organize the files by subfolder, but it isn’t fully dynamic you’d still need to “drill down” again whenever a new subfolder shows up.
If you want a 100% dynamic setup (so new subfolders automatically become separate datasets without manual work), the approach is:
1. Connect once to the parent SharePoint folder.
2. Extract subfolder names from the [Folder Path].
3. Group by subfolder.
4. Turn your transformation into a function, then invoke that function for each subfolder dynamically.
That way, whenever a new subfolder appears, the query automatically picks it up on refresh.

Regards,
Akhil.