Easily enter a File Path to Combine Files from SharePoint folder including sub folder option
Вставка
- Опубліковано 20 лип 2024
- 00:00 Intro
00:30 The basic way
03:52 Using List.Transform
07:44 Using Custom Function to get from folder including sub folders
Download File:
aasolutions.sharepoint.com/:f...
Mark Proctor
exceloffthegrid.com/category/...
Imke Feldmann
www.thebiccountant.com/2020/1...
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/ - Навчання та стиль
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.
Thanks for the tip Mark!
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.
Yep that’s a nice option.
Awesome! Many thanks, Wyn. I use several methods for doing this, and this is yet another technique I need to investigate. 😊
You’re welcome. Yes lots of different options here
Excellent - been looking for a solution to this for a while. Thank you!
You’re welcome
This is amazing. Thank you so much for sharing.
No worries, glad it helped
Tuyệt vời!!!!! Thank you so much
You’re welcome
Learn new stuff everyday 😊
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!
.files can be extremely slow on bigger SharePoint sites and the .contents and Imke’s method can be hugely quicker to refresh
@@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.
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?
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?
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
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.
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.
Hope it helps
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.
@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.)
It amazes me how some organisations waste so much employee time trying to “save money” on software.
@@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.
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.
Check for the removed other columns step. It might be in there