Sharepoint connection with excel files from multiple private groups of a single workspace

[Anonymous]

Hello,

This issue may have no solution but hope is what makes me alive !

My company has a workspace.

In this workspace there are dozens of private groups.

In each one of these private groups we can find an excel file.

My wish is to connect to all these excel files in one request.

Is it possible ? I manage to connect to them one by one but I don't find any root folder to catch them all in once.

I'd be glad to read your tips and tricks if it exists.

Thanks in advance.

Best regards,

Cado

[KNP]

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.

[Anonymous]

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

[KNP]

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.

[Anonymous · Accepted 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

[misiek5510]

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?

[Anonymous]

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

[misiek5510]

You Sir are a genius. 

The names were correct, however when I was setting up the channels I made a typo which was corrected streight away. Sharepoint site exist with the typo even when it was correctect and shows a different name in teams. Strange, but yeah it fixed it 🙂 Thanks 

[Anonymous]

Hi, 

how would you use the list/parameters to get files from all private rooms at once??

[Anonymous]

Hi @Anonymous 

I described the steps in message 6, can you add details on the step that causes trouble to you ?

[Anonymous]

Jepp, worked for me thank!

[KNP]

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.