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 Anonymous,
There's always hope. 😊
The typical pattern I use, and I get to do this a lot, is to connect to a site and then reference and filter from there as required.
If the "workspace" you're referring to is one SharePoint site then this will work for you as is, if it is actually multiple sites then you'll need to modify slightly to suit.
// SPSource
let
Source = SharePoint.Files(pSite),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], pFile, Comparer.OrdinalIgnoreCase))
in
#"Filtered Rows"
It's probably easier to go straight to the attached PBIX file and alter the parameters to suit but basically, I have setup two parameters, pSite which is the full address to the SP site, and pFile which is part of a file name, in this example I've used Text.Contains but you could just as easily do a Text.StartsWith or Text.EndsWith if it was more appropriate.
Go to 'Transform data' and change these two parameters to suit.
I hope this helps.
Let me know if you have any questions.