Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

11 REPLIES 11
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
Anonymous
Not applicable

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
Anonymous
Not applicable

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. 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
Not applicable

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

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

Hi, 

 

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


Anonymous
Not applicable

Hi @Anonymous 

 

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

Anonymous
Not applicable

Jepp, worked for me thank!

KNP
Super User
Super User

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.

 

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors