Connect to Sharepoint excel files with multiple Private Channels within one workspace

[lennox25]

I have one workspace but 15 private channels. All of these channels have been allocated to staff members and each has an Excel  Diary they need to complete. I need to pull all these excel files together as one in Power BI. I can pull them all individually but surely there must be an easier way?

[christinepayton]

Assuming the channels are all their own SharePoint site on the back end (these are private channels, right?), so it's not going to be super straightforward - the SP folder connector connects to a single site. I guess you could create a table of the site URLs and use the "invoke function" to run the same query on all of the sites (assuming the file structure is the same). 

I prefer not to use the SP folder connector when connecting to a single file (folder connector creates a lot of helper garbage), so you could use this technique to get the base query for the single file connection for the function to invoke: https://youtu.be/2HZ8l23RXnc?si=i0eQc7aAzx9a8oi_