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

10 ай бұрын

Thanks for the tip Mark!

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

@AccessAnalytic

10 ай бұрын

You’re welcome. Yes lots of different options here

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

@AccessAnalytic

10 ай бұрын

You’re welcome

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

10 ай бұрын

Yep that’s a nice option.

This is amazing. Thank you so much for sharing.

@AccessAnalytic

4 ай бұрын

No worries, glad it helped

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

@AccessAnalytic

10 ай бұрын

You’re welcome

Learn new stuff everyday 😊

@AccessAnalytic

10 ай бұрын

Always

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

10 ай бұрын

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

@GeertDelmulle

10 ай бұрын

@@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])

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

10 ай бұрын

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.

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

4 ай бұрын

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

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

@AccessAnalytic

9 ай бұрын

I’ve never seen that done sorry

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

9 ай бұрын

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.

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

9 ай бұрын

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

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

10 ай бұрын

Hope it helps

@txreal2

10 ай бұрын

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

10 ай бұрын

@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

10 ай бұрын

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

@txreal2

10 ай бұрын

@@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.