Forum Discussion
Sharepoint connection with excel files from multiple private groups of a single workspace
Finally I ended up doing a site list to use it as a parameter in a custom function.
It is working fine this way but someone will have to keep this list up to date through times to come.
If someone who have the same issue read this topic, an half solution is to :
1) Create a column which contains the teams private channels name in an excel file.
2) Connect to this file in a Power BI report.
3) In power query, invoke a function using the column names as a parameter.
4) Do the usual steps when connecting to a file
Here is the M code of the function I used :
(names as text) => let Source = SharePoint.Files("https://company.sharepoint.com/sites/workspace-"&names, [ApiVersion = 15]) in Source
Thank you for your time KNP and have a good day !Cado
Hi KNP
Indeed I'm talking in a Microsoft Teams context, and yes every group has a different root url but the difference is quite light :
Let's imagine the worspace is named "Workspace"
Every group corresponds to a project and is named "Projet A", "Project B", "Project C", ...
Then the root url of the groups is https://company.sharepoint.com/sites/Workspace-ProjectA for example
But I have no table listing the project names and I don't want to have to update it continuously. Maybe you know a way to get all these groups name with a query ?
Best regards,
Cado
I kind of figured that was going to be the case after I re-read your post.
If you don't want to maintain a project (site) list somewhere, I don't think there is an out-of-the-box way to deal with it.
Outside of that, I'd be looking at either Python (probably not), PowerShell and/or PowerAutomate options to solve this but I suspect it will be a lot of work. There may well be APIs to access SharePoint metadata for the entire domain but having that integrate and work in the Power BI service would be an entirely different challenge.
Just thinking as I type, there may be a PowerAutomate way to identify 'when a new team is created' or 'when I'm added to a team' kind of trigger that could automate the creation/maintenance of the list.
Other than that, I can't think of a working solution.
Finally I ended up doing a site list to use it as a parameter in a custom function.
It is working fine this way but someone will have to keep this list up to date through times to come.
If someone who have the same issue read this topic, an half solution is to :
1) Create a column which contains the teams private channels name in an excel file.
2) Connect to this file in a Power BI report.
3) In power query, invoke a function using the column names as a parameter.
4) Do the usual steps when connecting to a file
Here is the M code of the function I used :
(names as text) => let Source = SharePoint.Files("https://company.sharepoint.com/sites/workspace-"&names, [ApiVersion = 15]) in Source
Thank you for your time KNP and have a good day !Cado
Hi,
how would you use the list/parameters to get files from all private rooms at once??
Hi Anonymous
I described the steps in message 6, can you add details on the step that causes trouble to you ?
Hi. When I put the column name to invoke it it throws and error, but if I invoke a specific row it works fine, but it only imports one at a time. What this the case for you too?
Hi misiek5510
I didn't have any error.
Perharps you should check that all your names exists and that there are no typos in it, there must be at least one name causing troubles.
Regards
Cado
