Combine Files from SharePoint folder and sub folders ( Easily enter a File Path )

[@ExcelOffTheGrid]

Thanks for sharing Wyn. For Excel users a useful method is to get the path as a worksheet parameter, then you can control the path in Excel without needing to open Power Query each time.

[@AccessAnalytic]

Thanks for the tip Mark!

[@paspuggie48]

Good stuff again Wynn, some amazing solutions. I do '.contents' and add a Custom Column and 'excel.workbook', as you showed but for my Users it could be for a PDF file or CSV.Document as well. What I love doing for my Users is give them the option to type in the cells on their Worksheet their SharePoint Folder (Path), Sub-Folder Name and Filename. I then define those cell names and pull them through as connectors into the PQ Editor and use them in my M Code. This way the solution can be adopted and adapted easily by other Teams if they have different SharePoint paths.

[@AccessAnalytic]

Yep that’s a nice option.

[@iankr]

Awesome! Many thanks, Wyn. I use several methods for doing this, and this is yet another technique I need to investigate. 😊

[@AccessAnalytic]

You’re welcome. Yes lots of different options here

[@user-yy1io4yk5f]

Excellent - been looking for a solution to this for a while. Thank you!

[@AccessAnalytic]

You’re welcome

[@EngineerHarris]

This is amazing. Thank you so much for sharing.

[@AccessAnalytic]

No worries, glad it helped

[@vinhwizard]

Tuyệt vời!!!!! Thank you so much

[@AccessAnalytic]

You’re welcome

[@joedi]

Learn new stuff everyday 😊

[@AccessAnalytic]

Always

[@GeertDelmulle]

Hello Wyn, Honestly I fail to see the benefit, here: If you use the .Files-function, you get all the files in the entire tree, and from there you can simply filter on [Folder Path], [Extension], [Name], etc. in order to get what you want. No iteration required. Why do it this way? OTOH: that Imke function is very impressive, being a "one stop shop" that drills down to the desired folder, including the Documentation bit!

[@AccessAnalytic]

.files can be extremely slow on bigger SharePoint sites and the .contents and Imke’s method can be hugely quicker to refresh

[@GeertDelmulle]

@@AccessAnalytic Thanks for that response, Wyn. I see your point. (I had to look up this video in order to see this reaction - no notifications despite ringing the bell. YT doesn't want us to be friends) Here's a further remark after studying the solutions: • IMO Marc's and Imke's solutions both need the same inputs: site-path and folder path. I fail to see the benefits of Imke's solution. • here's a little improvement on Marc's navigation step: NavigateToFolder = List.Accumulate(FolderPathList,Source, (state,i) => state{[Name=i]}[Content])

[@nadadrissi2053]

Thank you for the video! I tried the same method but I am facing an issue with slow data extraction and transformation in Power Query with slow data extraction and transformation in Power Query especially that I have around 15GB of files. Any solutions?

[@AccessAnalytic]

With 15GB of files you’ll need to load them into a SQL database rather than leaving them in SharePoint

[@Karen-bm3rj]

I think this might be what I need to solve an issue I have with an Excel workbook using power query to filter and return data from a dataset of over 2 million rows (and increasing weekly). The source data is stored in a folder on my drive, and the Exe El workbook is on my OneDrive and shared with numerous colleagues. This is now constantly failing - timing out messages and pivot table failure messages 😟. I'll have to try this.

[@AccessAnalytic]

Hope it helps

[@txreal2]

over 2 million rows? and the source data is stored in a folder on my drive (could be a slow sync with OneDrive) Have you consider Power Query in Power BI in the new MS Fabric? I would experiment with a data subset to see how it works first.

[@Karen-bm3rj]

@txreal2 I'd like to try, but sadly I'm limited with what apps my organisation will allow. We are still working on Excel 2013 desktop but have limited access to MS 365 online though nhs mail but only few apps and not all functionality. (I even had to beg for power query to be installed.)

[@AccessAnalytic]

It amazes me how some organisations waste so much employee time trying to “save money” on software.

[@txreal2]

@@Karen-bm3rj Google says "NHSmail is a secure email and collaboration service approved and owned by NHS England for sharing patient identifiable and sensitive information" Good luck. I avoided apply govt jobs in US bc I know it would drive me crazy.

[@McIlravyInc]

Is list.accumulatw faster? Does it need list.buffer for performance with large files? Happy ttry anything to help speed up refresh and especially power query previews.

[@AccessAnalytic]

I don’t think it will speed things up with the first method ( but Imke’s function does incorporate it ). It’s a grey area for me, but I don’t think just iterating down the path will be quicker using it.

[@joserafaelfarfanfernandez899]

Hi, something has changed with the ".Contents" function ? I have used a lot and now is giving me issues, when I try to use it I get an error message saying "We couldn't authenticate with the credentials provided. Please try again". Someone knows how to fix this ? Is really odd because it connects fine with the ".Files" so it shouldn't be an issue with the credentials.

[@AccessAnalytic]

Do you have full access to the entire file path? What version of Excel ( via File - Account )

[@goutamnayak5011]

Sir please clear my doubt Copilot 365 soon available in Excel & other ai tools are there So is it still important to learn ms Excel deeply all functions and advance formulas

[@AccessAnalytic]

I've never spent time to really learn formulas, I've just learned what I needed when I needed it and kept up to date with new features as they are realeased. Copilot in Excel won't be available to most people for a long time, but even tools like Chat GPT are already helping me to explain formulas and write code, so I don't think there's much value in sitting reading a book of formulas.

[@Defenestratio]

How can i bring in sharepoint document library metadata associated with the files?

[@AccessAnalytic]

I’ve never seen that done sorry

[@VirginieBaltus]

Hi, I've used a function to get files from a folder, but I've noticed that for some of the files, the last column isn't showing up in Power Query. Do you know why this might be happening?

[@AccessAnalytic]

It could be down to the expand column step referencing the first file and hardcoding the headings. Try this How to use Power Query to Combine Multiple Files that have different headings kzbin.info/www/bejne/ZmrXp5yXlJ2soas

[@VirginieBaltus]

​@@AccessAnalytic Thanks for the suggestion! I don't think it's related to the column expansion or hardcoding the headings, since I've noticed the same problem when importing manually via "Get Data." Also all my files have the same headings. I'll keep looking into it

[@juanfelipevalderramaz.2475]

Hello! I followed all the steps but when I combined all the files that I needed and pop them into the query, the FIleName column didn't showed, how could I fix it? I need the File Name column.

[@AccessAnalytic]

Check for the removed other columns step. It might be in there