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.
@AccessAnalytic11 ай бұрын
Thanks for the tip Mark!
@paspuggie4811 ай бұрын
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.
@AccessAnalytic11 ай бұрын
Yep that’s a nice option.
@iankr11 ай бұрын
Awesome! Many thanks, Wyn. I use several methods for doing this, and this is yet another technique I need to investigate. 😊
@AccessAnalytic11 ай бұрын
You’re welcome. Yes lots of different options here
@user-yy1io4yk5f11 ай бұрын
Excellent - been looking for a solution to this for a while. Thank you!
@AccessAnalytic11 ай бұрын
You’re welcome
@EngineerHarris5 ай бұрын
This is amazing. Thank you so much for sharing.
@AccessAnalytic5 ай бұрын
No worries, glad it helped
@vinhwizard11 ай бұрын
Tuyệt vời!!!!! Thank you so much
@AccessAnalytic11 ай бұрын
You’re welcome
@joedi11 ай бұрын
Learn new stuff everyday 😊
@AccessAnalytic11 ай бұрын
Always
@GeertDelmulle11 ай бұрын
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!
@AccessAnalytic11 ай бұрын
.files can be extremely slow on bigger SharePoint sites and the .contents and Imke’s method can be hugely quicker to refresh
@GeertDelmulle11 ай бұрын
@@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])
@nadadrissi20536 ай бұрын
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?
@AccessAnalytic6 ай бұрын
With 15GB of files you’ll need to load them into a SQL database rather than leaving them in SharePoint
@Karen-bm3rj11 ай бұрын
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.
@AccessAnalytic11 ай бұрын
Hope it helps
@txreal211 ай бұрын
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-bm3rj11 ай бұрын
@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.)
@AccessAnalytic11 ай бұрын
It amazes me how some organisations waste so much employee time trying to “save money” on software.
@txreal211 ай бұрын
@@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.
@McIlravyInc11 ай бұрын
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.
@AccessAnalytic11 ай бұрын
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.
@joserafaelfarfanfernandez89919 күн бұрын
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.
@AccessAnalytic19 күн бұрын
Do you have full access to the entire file path? What version of Excel ( via File - Account )
@goutamnayak501110 ай бұрын
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
@AccessAnalytic10 ай бұрын
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.
@Defenestratio10 ай бұрын
How can i bring in sharepoint document library metadata associated with the files?
@AccessAnalytic10 ай бұрын
I’ve never seen that done sorry
@VirginieBaltus16 күн бұрын
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?
@AccessAnalytic16 күн бұрын
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
@VirginieBaltus16 күн бұрын
@@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.247511 ай бұрын
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.
@AccessAnalytic11 ай бұрын
Check for the removed other columns step. It might be in there