cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cado
Resolver I
Resolver I

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

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

 

1 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

View solution in original post

5 REPLIES 5
KNP
Super User
Super User

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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

View solution in original post

KNP
Super User
Super User

Hi @Cado,

 

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors