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 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