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

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

Hi, 

 

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


Hi @Coan7 

 

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

Jepp, worked for me thank!

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors