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
I may have misinterpreted what you meant by "private groups".
If you're talking in a Microsoft Teams context then the private groups are actually separate SP sites behind the scenes and there is no concept of a "root".
This isn't an ideal design choice in my opinion and is contradictory to what is visually displayed.
Let me know what your actual setup is if my first suggestion doesn't work.
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