Get Excel data from several sharepoint folders

[M0skus]

Hi! 

I havent had any luck figuring this out on my own, and I'm pretty new to PBI. 

Would appreciate a nudge in the right direction.

Our organization have 30 branches, each with their own Teams group. 

Each branch has an Excel report in their own area, which they update every day. 

Is there a way for me to copy/sync these files into one folder on onedrive, then use that folder as a continuously updated data source in PBI?

Edit:

I tried creating a folder with shortcuts (url) to the files, but when getting data in Power BI I only get the URL, not the contents of the Excel form the shortcut points to 😕

[M0skus · Accepted solution]

So I finally managed to get all the data from the different folders in Sharepoint. 

What ended up being the solution was setting up individual queries for each of the sharepoint folders, then appending them all as one new query. 

From there I used @jennratten's tip on text filters, and was left with only the files I wanted. 

[jennratten]

Hello - I can't see the depth of the Sharepoint url you are querying so I can provide only general info. 

The depth of the Sharepoint url needed to get the files you are wanting depends on a few factors. 

Basic Info (I think this is likely your problem, assuming you have permission to site(s))

Each Team in the organization has it's own Sharepoint site root url. Note, the team name and private channel names included in the URL omit spaces. 

This structure will return files that are in any team channel that is not private.

https://Organization.sharepoint.com/sites/Team/

This structure will return files that are in a Team channel that is private.

https://Organization.sharepoint.com/sites/Team-PrivateChannelName/

When filtering using Text.Contains, keep in mind that it is a case sensitive search unless otherwise specified.  There may also be things unaccounted for, such as spaces/no spaces, spelling errors, etc.  At a minimum, I recommend using a case-insensitive search, omit spaces and include the file extension you are targeting.

In this snip below, I was looking for pdf files containing "Color Palette" in the file name and want to be sure the results were case-insensitive and also exclude any files that were not pdf.  Note, the file extension can also appear as upper or lower case.

So to use this method in your situation, the script would be:

Table.SelectRows(Source, each Text.Contains( Text.Replace ( [Name], " ", "" ), "runninghours", Comparer.OrdinalIgnoreCase ) and Text.Contains ( [Extension], ".xls", Comparer.OrdinalIgnoreCase ) )

Using Text.Contains with .xls ensures that xls and xlsx files are both returned in the results.

It looks like the step before filtering for a substring in your file name was also a filter step.  Be sure you didn't do anything in that step that would have eliminated the potential results.

Additional Info

The files may have various versions and may include files that include 'running hours' in the file name that should not be included in the search results.  Those are just some other scenarios you may want to account for in your script.  

[M0skus]

Hi Jennratten, thanks for your detailed reply! 

Good to know about the text filter being case sensitive, I thought otherwise.

Applying your steps I managed to find a copy of one of the files stored in another sharepoint area. Which means I now know the text filter works how I want. 

I apologize if I misunderstand you, but I still cant quite grasp where I go wrong.

I struggle to understand the sharepoint url, depth and so on. (I struggle to understand sharepoint entirely to be honest)

Our organization has one "root" the way I figure - "https://Organization.sharepoint.com/".

When using this url only, I find files stored on our branches area, but cant seem to access other branches files. The other branches have an added "/sites/branchname" in their url when I locate the files on sharepoint. The first part of the url is the same. 

I thought this would be kind of like searching through a hierarchy; Search from the top and locate files downwards through subfolders. This seems to not be the case?

---

@jennratten wrote:

It looks like the step before filtering for a substring in your file name was also a filter step.  Be sure you didn't do anything in that step that would have eliminated the potential results.

---

 The first filter step was applying an extension filter for excel files 😊

[jennratten]

I'm glad this info was helpful.  To answer your question, when you query the organization's root, you only get the files that are shared at the organization level, not any underlying Team sites.  In the example below, first I have queried the org root and see only 24 files and then query for a Team site and see over 1000 files which include all files for the Team (less files within private channels).

Org Files (unfortunately no hierarchy is included that can be expanded)

Team Files

If you create a table with a column containing the Team site names, I can help you use one query to connect to each.

[M0skus]

Thanks for further clarifying, very useful information! 

Appreciate the offer of a single query, but it works allright the way its set up now 🙂 

Again, thanks for the great help! 

[v-jingzhang]

Hi @M0skus 

Are these files on the same SharePoint site? If they are, you can use SharePoint Folder connector. This connector will get all files on a site. Then you can filter by Name or Folder path to keep only files you want. Combine them into one query. See https://powerbi.microsoft.com/blog/combining-excel-files-hosted-on-a-sharepoint-folder/ 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[M0skus]

Hi! 

Thanks for replying. 

I tried you suggestion again, but I cant seem to find the files that I know are located on our company sharepoint. As soon as I try adding a text filter, no results are shown. 

Edit:

I tried adding a /sites to the sharepoint URL, as all branches have their own area on sharepoint - (/sites/Branchname) This would narrow the search down greatly, as it skips all contents of the root path. 

That didnt work, produces the following error: