The best way to connect to a SharePoint Folder to speed up your Excel and Power BI data refresh
Вставка
- Опубліковано 1 жов 2024
- How to consolidate multiple files from a SharePoint folder using Power Query in Excel and Power BI.
🔽 More Info below PLUS warning 🔽
Warning: to use SharePoint.Contents the user must hace access to to all levels of the SharePoint site.
Additional technique to speed up refresh using incremental refresh
• Faster SharePoint fold...
Sadly the button is not included in 365 for Business . However you can still use the code
= SharePoint.Contents("YOURPATH/", [ApiVersion = 15])
Video I mention explaining the consolidation helper queries
• Combining Multiple Fil...
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic...
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/
This video is a genuine service to humanity
😂 - made me smile - thanks!
I agree 1000%. This video is gold and is something I never want to lose.
Thank you 😀
@Access Analytic question, have you ever set up a "SharePoint" Server? Meaning you have a SharePoint dedicated to running queries that are aggregating from forms you created for your company on another SharePoint site. You can then query to this "SharePoint Server" site to get reports from?
I am just speechless. What an unbelivably timesaving workaround.
To be honest this video has just paid for my year's UA-cam subscription. Can you imagine what the world would be like if every video was this informative. I had practically given up on trying to get files out of Shitepoint until now.
Very kind, I appreciate you taking the time to let me know you found it useful
Even in 2024 this Video still makes me say : you are the Master !!!
Cheers Juan!
You are the only person who came with a decent solution that really works. Thanks!
You're welcome
This video is excellent - the way you explain things is perfectly digestible, helped me alot!
You’re welcome. Thanks for taking the time to leave a kind comment
Love the column size and contents hack! I've been scrolling all along. So glad I stopped down on this. Thanks so much.
You’re welcome. I appreciate you taking the time to let me know you found it useful
Thanks for the video. It's informative and usefull.
I appreciate you taking the time to let me know you found it useful
Hi,
Is there any way to consolidate two excel sheets coming on a day per week and importing to powerbi so that after refresh there's the new date added on the dashboard?
Does this video help ua-cam.com/video/sLW3NbeGDy8/v-deo.html
What an awesome hack! I can't believe Microsoft hasn't already baked the SharePoint.Contents functionality into their PQ menus. Thank you!!!
You’re welcome. I appreciate you taking the time to let me know you found it useful
This is AMAZING! Thank you. This has been driving me absolutely insane!
Question: If I have CSV files grouped by different folders - those folders act as categories or groups. I followed your steps and can easily get to what I want for a Single Folder (that contains CSV Files) but what about if also want to repeat the transform for all the other folders.
Do I have to keep duplicating the steps you went through here to get all the queries in the same Excel File for all the folders of interest? Or is there a simpler/better way?
The data (CSVs) in each folder will get their own Sheet in excel.
Thanks!
If you’re wanting to consolidate files from sub-folders then try out Imke’s solution per this video ua-cam.com/video/mgVnk4R79ac/v-deo.htmlsi=uq_bCTMHiYoLD8Lf
HI Wyn, Thanks for the various videos and for this one. I tried using the Contents option you show but then when I try creating the Query I get an error stating my file format is not correct. I tried it several times with the same error. I used a hybrid of the Contents version to navigate to the correct folder, filtered by its location and then went back and changed Contents back to file. I have looked high and low and cannot find the reason for the error despite all the files being xlsx - any ideas how to fix this? Thanks
Shane
Odd. I’d manually create a couple of files with a little bit of data and put them in a folder and try it.
Very good video, helped me on import data from Excel and csv files hosted on Sharepoint folder. Appreciate it very much.
However, this video is misleading to me as the formats of my Excel and csv files are different so do need to be consolidated.
So I suggest you change the title to add "consolidate" in it as quite some contents in this video is about "consolid" your 3 excel files.
Again, thank you for your video, expecially the tricks in it.
If you’re connecting to individual files I’d recommend ua-cam.com/video/vPV67RLGoOg/v-deo.htmlsi=whWP7aSIWmeKw_4x
One thing to note when I was doing this. Datflows Power Query it doesnt show binary vs tables, So what I did was do the base function in excel then copy and finish work in Dataflows
Yes it's an odd bug that I reported to Microsoft in April.
The hack fix is to add one more step (e.g. uppercase a column ) and hey presto the binaries appear!!
You have saved my life! I wish I could give you a million likes. Thanks a lot for your clear and detailed explanation. Well done!
Glad to help Chinedum, thanks for taking the time to let me know it helped you. Hopefully you’ll let others know about this channel 😀
Nice, but its still dumb that you need to connect thousand of files (possibly) and to navigate or filter a sub folder, they havent improved this?
Still the same sadly
How can I like this video many more times? Thank you for it!
😆Thank you!
Thank you so much, this has been bothering me for such a long time. I don't understand why MS does not just allow you to connect to the subfolder directly instead of only allowing you to conect to the main Sharepoint site.
You're welcome, hopefully they'll improve it soon
This is awesome, but I am trying to pull in thousands of files in with this and I get an error for: The remote server returned an error: (500) Internal Server Error. (The attempted operation is prohibited because it exceeds the list view threshold.), would you happen to know how to deal with this?
No sorry
Thank you so much for this video! Quick question, what if why my sharepoint folder contains multiple excel docs with multiple sheets and i want to extract all of them? What do i do? Once i get to the Combine wizard it makes me pick one sheet out of all the sheets and only extracts that sheet out of all the excel documents.
Check out Mynda’s video ua-cam.com/video/sLW3NbeGDy8/v-deo.htmlsi=hYz_scSKDlcWAB5z
Thanks for nice and simple way of explaining it. How can I dynamically pass SharePoint Link and Folder as a parameters so that we do not end-up rebuilding query every year or if same wants to use by multiple department but map with different SharePoint Site and Folder
Technically you can add a Parameters in Power Query (there's a button and wizard to create a parameter) then just replace the hard coded paths with the folder names. The difficulty with the SharePoint.Contents approach is there is an unknown number of folders to drill through so that will be a problem.
thank you so much!!! I wish you get good & blessing karma because of your very useful knowledge!!
Thank you
Thanks a lot for all your videos, incredibly clear and helpfull !
I was wondering, if I need to import only one or two files on a sharepoint, should I use preferably this connection to the sharepoint folder method or the simple web connection to the file directly? which one is the more efficient? thanks:)
You’re welcome. Use from web when connecting to 1 file.
Hi thanks for this. I am doing all this and have the data currently connected to my report. However I am having an issue where when I refresh the data on PowerBI online it is not refreshing the data despite it being successful (no errors). Do you have any suggestions for why this may be?
Any filters on your Power Query or Report that would limit what data is appearing. And you are refreshing your dataset in your workspace?
Great Vid. So when u load say April and May, would the source update with a refresh?
Yes absolutely
The solution is super elegant. Thank you so much !!!
You're welcome. Thanks for taking the time to comment.
MY GOD!!! Amazing! spent ages getting errors using other methods, love the simple exceptionally clear step by step presentation - Thank you!
You’re welcome Neil. Glad it helped and thanks for taking the time to leave a kind comment
ur videos are so cool! QQ: how do I read an excel file in SharePoint using SSIS?
I’ve never used SSIS sorry
I dont have the sharepoint option how can I fix that?
Create a blank query and add this code:
= SharePoint.Contents("YOURPATH/", [ApiVersion = 15])
Amazing! This is what I want. You saved my lifee haha
Glad to have helped 😀
Thank you a lot for this video. I have applied the same steps but in my case I need to delete the old files from the folder and paste new ones with different names (all of them have the same format) with refreshed data in the connected folder. Every time the Power Query shows an error because it refers to an old file name and I have to change it manually to the new file name every time, is there a way to automate this?
Technically it shouldn’t happen so you’ll need to look through your applied steps in Power Query and see which one refers to a file name and remove / alter that step
Thankyou so much , you saved countless hours!!!!!
Great to hear! Thanks for taking the time to let me know
Amazing. Insane they dont use those connector instead of .files no idea why they use the older one its slow if there is a ton of data.
The main limitation with .contents is you need full SharePoint path access all the way to root folder.
Thank you so much for sharing this took some finding and several other methods are not so effective
You’re welcome
This really useful however I found the data incorrect. It appears my report keeps brining in the wrong file versions from SharePoint.
I’ve never come across that. If you go to SharePoint online and open the files there are they not the same ones used in the report.
@@AccessAnalytic Thanks for the reply but ignore my query. I misdiagnosed the issue. There are time based calculations in the Excel files. Clearly correct at the time they were saved, which is what BI is bringing back but what the file is opened the calculation updates and shows a different result. I'm now using the file modified date to calculate the offset 👍
Wow. This is a neat workaround. Thanks for sharing this! I would generally avoid this method because the "drill down" isn't expanded and my worry is it isn't shown in Applied Steps. I guess by breaking them like what you showed here does the trick! So now whenever the folder path changes in future, I can go to that step and edit!
Absolutely Alex, I used to feel the same way
Thank you so much tha was so helpful i solve my problem , love you man
☺️ glad I could help 🤘🏼
Agree with so many other comments! Thank you for sharing your knowledge in such a clear way! Many thanks!
You’re very welcome. Thanks for taking the time to leave a kind comment
this video just saved my day. though i am wondering, is there any way to make sure, that the first two rows (headlines) of each file will not be included, so that there will be only one sort of headline, which is basically identical in all files.
Great. Yes you edit the transform sample file query that shows up around 5:10
Also I mention this video that goes into an explanation of those steps
ua-cam.com/video/nPlrQUbEn4o/v-deo.html
@@AccessAnalytic must've skipped this part, thank you!
Very helpful! Thank you very much.
Quick question, before moving my file to SharePoint. I was able to copy the file to a new folder, and the queries would automatically update the data source as it referenced the file path. ex. =Folder.Files(FolderPath) is there any such way to automate this by referencing the Sharepoint file path? Or will I have to go in and manually update the "SharePointFolder" each time I move the file?
I wasn’t aware of power Query auto updating when source files were moved. You will need to manually update it.
@@AccessAnalytic Apologies, I may not have explained that well, I am a beginner. Basically, I was copying the binary file to a new folder each week. (New folder contains the same number of files in the exact same format with the exact same name but with new data) Refreshing would pull in the new data without issue. The source for my sample file, and export query were set to the folder path of wherever the binary file is saved, so the source was updated each week just by saving the file to a new location. That does not work now that we moved to Share Point.
However, following your video, I had no issue adding a share point folder source, it would just be nice to not have to go in and update the share point folder source each week.
@aarongarcia4439 ah.. so do you have a formula in the Excel file to identify the path it’s saved, and then Power Query uses that reference?
Potentially you can do the same thing if everyone who refreshes first syncs the files to their local machine, otherwise I’m not sure if it’s possible to reference the SharePoint path when you open the file locally to refresh.
thank you ! :D
You're welcome
Hello and thanks for the super useful video. I tried to replace files by content as you proposed, but the authenfication window appeared (I could still download the sharepoint folder without issue) and no way to authentify myself
Do you have access to the full SharePoint path? Is the refresh still working or not?
@@AccessAnalytic I found the solution: I needed to have entire access to the onedrive/sharepoint, so my boss gave me full site collection owners authorisation
Thanks for the reply!
All good
Finally! It was so hard to find such elegant solution to the problem of slow working with SPO folders...
I’m glad you found it useful.
This video save my time and money. Many Thanks!
Glad to help 😀
Great video! I am experiencing a problem though. when I try and combine files, I get an error which reads "we didn't recognize the format of your first file type (). Please filter the list so it contains only supported file types." My files are all .xlsx just like yours, so it should be working. Any ideas on why this is happening to me? Thanks again for the video!!
4th Feb 2023 update:
Hey all I’m now getting the same error with xlsx and it’s a bug that’s being looked into
Original reply:
Not sure, maybe a hidden system file in the folder. Try copying the first few files to a different folder and try there to see if the problem goes away
Hey I am getting the same error, did you find any solution for this or any lead? I am searching for the solution for days now.
4th Feb 2023 update:
4th Feb 2023 update:
Hey all I’m now getting the same error with xlsx and it’s a bug that’s being looked into
@@AccessAnalytic I experienced the same thing with .xlsx files. I changed the files to .csv and it worked like a charm.
Hey all I’m now getting the same error with xlsx and it’s a bug that’s being looked into
Thank you so much. Is there any means we could keep a sharepoint EXCEL file in connection with Desktop EXCEL file in both directions so that a change in data reflects in Sharepoint file and vice versa.
Hi, the normal approach is to Sync your SharePoint file or folder so the files are then essentially the same file
@@AccessAnalytic Thanks. I will try. I do not know whether it will capture the changes make by other team members in real time.
Yea it will, just make sure everyone has Autosave on. support.microsoft.com/en-us/office/collaborate-on-excel-workbooks-at-the-same-time-with-co-authoring-7152aa8b-b791-414c-a3bb-3024e46fb104?WT.mc_id=M365-MVP-5002589
Very helpful video! thank you
You’re welcome
Worked like a charm. Thank you sir.
I appreciate you taking the time to let me know you found it useful
Thanks for the amazing tip! Is there any way to get the 'Modified by' column?
Hi Aaron, doesn't appear so, Date Modified yes, Modified by, no. If you find a way come back and let us know
Hi, this content is awesome! Thank you.
I wonder if there is some kind of limit in how many files can Power BI can fetch and consolidate from a folder.
I may have a scenario where the client uploads like 40 different files a day to be used in a report.
Technically not, but the refresh will take longer and longer to refresh. You could do staged refreshes where a few months worth is stored in a dataflow for example and then added to the consolidation but really a database is a better option.
@@AccessAnalytic thank you very much for your response! We are checking if we could use Power Automate to consolidate these daily files into one excel file or a SharePoint list to prevent this.
Thanks again for the suggestion and happy new year!
@ksperder4035 no worries. Something like this might give a few ideas ua-cam.com/video/g4oZ0pOpn-4/v-deo.htmlsi=42QsVit7ieSVJdzo
Such a good tutorial ! Thanks a lot !
You’re welcome. I appreciate you taking the time to let me know you found it useful
This worked marvelously for me the first few times then I tried again months later only to get this error message: "We didn't recognize the format of your first file (). Please filter the list of files so it contains only supported types (Text, CSV, Excel workbooks, etc.) and try again." The folder only contains .xlsx files so I'm loss. anyone else having this issue? As a workaround, I used an older file and changed the source location to the new folder within the same file path and it worked.
Yep it's a bug Microsoft are fixing. No ETA at this time
This is a great video! Exactly what I was trying to do. Question if I delete data on my PBI will it remove it from the sharepoint file?
Thanks. Power BI is read-only so filtering out any data in Power Query will have no impact on your SharePoint files
One of the spreadsheets had a table under the other. Now, in PBI I have two different data in one query. Can I remove half of the table into a new query, and would it keep its connection?
Difficult to answer without going into more detail but you can right-click reference a query and build other queries off it.
Absolutely brilliant! Thank you so much. Finding subfolders in SP has been a killer for me until now. Much appreciated.
You’re welcome. I appreciate you taking the time to let me know you found it useful
Perfect!!! Thanks
You’re welcome.
Great Video.! My only comment is that pretty soon we will need to go thru' a Master's degree course to be able to use Excel. !!! they are making things more and more complicated that the ordinary man in the street will NOT be able to handle. That man is me, although my knowlwdge is quite good , which includes VBA programming and other areas... and the other nightmare is the 'Permissions' set up in sharepoint ( which again) you need to be an 'expert' to understand this.. MS is going overboard,,, Pretty soon very few people are going to be able to use Excel and Power BI I'm afraid ! But anyhow thanks for sharing your knowledge. Thanks Wyn
You’re welcome Martin. The are certainly many things that Microsoft could simplify. They do listen a lot more to feedback these days and are much more proactive in fixing up issues.
The pace of change over the last few years has been incredible. As a full time Excel and Power BI consultant even I struggle to keep up
Can anyone that has sharepoint access can refresh the file?
Yes
This is so much cleaner than my "filter 'ends with'" hack
Also seems to load waaaay faster, which is my biggest gripe with PowerX and SharePoint
Yes, definitely quicker as t doesn't have to navigate all that meta-data
Thanks for the wonderful tips. Could you also show how to get all the data if the Excel files contain multiple worksheets? Thanks
Consolidating multiple sheets from one file is here : ua-cam.com/video/cPN24NK3_68/v-deo.html
Consolidating multiple sheets from multiple files is tricky. Likely multiple “ from folder “ queries or the use of custom functions
@@AccessAnalytic Thanks for the quick reply and appreciated the info.
No worries
Awesome! :D
Excellent video and clearly explained. Thank you
You’re welcome. Thanks for taking the time to leave a kind comment
Excellent demonstration 😅
Thank you for that
Great video, put's light in a very complicated mechanism to access excel files. once again this proves how bad this non-feature was implemented from PowerBI point of view. The casual player will never figure out this. It has no explanation why power bi developers and sharepoint developers didn't manage to find a simple and easy way to access each specific folder !
Agreed. In the next 6 months I expect to see the dataflow UI hit power bi desktop and that has a better connector experience
Thank you so much sir, this video help me a lot in my job. I didn't know what to do with SharePoint folder when my boos asked.
You are welcome. I appreciate you taking the time to let me know you found it useful
Thanks, brilliant guide. You mentioned scheduled refreshes, but that isn't working for me. The credentials don't save to allow a scheduled refresh in PowerBI. Any tips ?
Make sure you select OAuth2 when entering your credentials
@@AccessAnalytic Thanks, I just got it to work by entering credentials as OAuth2 on the data settings.
@@AccessAnalytic Arggh, and my previous method, using a Web endpoint for Sharepoint files (while it works for PowerBI desktop) doesn't allow OAuth2 when published online.
If you use From Web to connect to SharePoint files OAuth2 works
@@AccessAnalytic wow, yes, you're right. I got to the bottom of my previous problems with the Web method, looks like you cannot enter OAuth2 creds for a Private Teams Channel, however a standard Teams Channel worked flawlessly. Thanks again for your video, got me on the right track. The web method also seems to work better for sites with lots of Excel Documents, I spent an hour navigating through SharePoint.Contents("path") and eventually gave up. By that stage you'd helped me diagnose the Web issue, thanks again.
I've been trying to solve performance issues for a week!! I'd LOVE this video if I could
Glad to help, thanks for taking the time to leave a kind comment
Thank you for posting a better/quicker way to connect to sharepoint folder! It worked very well for me!
You’re welcome
Amazing, thank you!
You're welcome
Holy c... - the speed on this solution is Amazing !!!! Instead of 5 minutes - I'm getting data in 30 seconds - Thank you very much !!!!!
You’re very welcome and thanks for confirming the speed gains
Thank you sir. You are a saint
😇
Thank you, thank you, thank you for making this video, so incredibly helpful!
You’re welcome
Really, thanks for sharing your knowledge. You saved my day here. Was trying for 2 hours how to link it. The Power BI/EXCEL in app information is horrible for this. This video/tutorial deserves an OSCAR.
😀 thanks Matheus
Thanks for solving puzzles for me. Love you man. You are great 👍
Too kind ☺️
I get a message "We didnt recognize the format of your first file" (both files are .xlsx files?
I have heard of that happening with no obvious reason. Try creating a couple of your own Excel files in another folder and see if you can get that to work.
Also are the excel files exports from a system and are they showing xlsx extension and not xls
That's quite useful. Thanks for sharing :)
In my case, I'm looking for something else: like using the CELL function within Excel with a SharePoint folder so it can be loaded in PQ. I know this is possible with the folder path but not with SP folder.
Have you any idea how you can do it? :)
I’ll ask around but I know it’s a source of frustration for many. Can you give me a bit more detail around what you want to be able to do with this?
@@AccessAnalytic Indeed it is.
Sure! I have created a Dashboard where I have the KPI of agents in a call center - very useful, very handy and very visual - and I update it once a week with the weekly KPIs and MTD.
When new reports are ready, I add the new data to the folder I have for my DB. Then, I simply hit 'refresh' and PQ does the job - very easy! :)
Anyone can add new data to the folders. Meanwhile, when someone else wants to hit 'refresh' in Excel in order for the PQ to work, it won't work?
Why? Because the path in the source if my computer's. So, the other users have to change the path everytime they want to refresh the DB (or ask me to do it, which is not convenient at all). Therefore, the path is changed everytime a new user wants to refresh it.
It is tedious!
If the files were not in SharePoint (as the company requires), that would be different - not shareable, but different - by usinge the CELL function and creating a variable in PQ. However, while having the file in a SP folder, it shows an URL (and sadly I cannot work with that :( maybe I dunno how yet).
If you are able to share some idea, or at least make a video so it can be shared with everyone, that would be super awesome :)
Thanks again, and have a great day ahead :)
But if the source is SharePoint and everyone has access to the SharePoint folder then it should work for them?
Thanks for this it will save me so much time! If the resulting file is to be shared by a group of people (within the same organization) do they all have to do the 'sign in' step? It seems unnecessary and inconvenient since they can already access the documents in sharepoint. Is there no way around it?
No worries John. Yes they all have to sign in, I know what you mean though.
Hi Nice video!! I'll try this later. Do you Know if theres any Kind of limitation about how many files can be imported from sharepoint folder?. Again, great video, im learning a lot since i found your channel
Thanks Christian. I don't know of a technical limit, I've certainly see 100 done. But the more files you add and the more data there is, and the more complex the transformations there are then the slower it gets. I have staged queries in Excel in the past e.g. 1 consolidation per 6 months of data for example. If using Power BI keep an eye out for my Dataflow demo
Thank you so much - amazing
Thanks, I appreciate the comment
Thanks for sharing. You made my day. With standard method it is a pain if a SharePoint has 10000s of files....
Totally Frank. Glad to help
I wish I could give a x100 thumbs up. So useful for my job. Thanks
Awesome!
This helps! Thank you!
You’re welcome
Thanks for the video.
I am using M365 Business Premium and when click on "Get Data" >> "From File" Can't find "From SharePoint Folder" !! Can you please advise on this
Bizarrely Business version doesn’t have the SharePoint button but the following code will work even though Business Version doesn't have the SharePoint Folder button
= SharePoint.Contents("YOURTOP LEVELFOLDERPATH/", [ApiVersion = 15])
If you create a blank query and then replace the Source Step formula with this code it should work, then you get a list of Tables and click on the word Table next to Documents (normally around Row 6) and then keep on clicking each Table against each Folder to get to the right folder. Finally click on the double down arrow on the the first column called Content (when you can see all your files listed). That should trigger the folder consolidation.
Note that your TOPLEVELFOLDERPATH will be something like mycomany.sharepoint.com/ with maybe one subsite name after the /. It's not the entire URL from your sharepoint folder.
Nice video. One quick question. I tried combining files from SharePoint folder using PBI desktop. However, the same when tried with the PBI services, I don't get the combine file option at the last step rather I can see the expand table option. Any work around required while doing on PBI services?
Hi,are you referring to dataflows?
Hi, SharePoint Folder does not appear in my Excel. I had Office 365 for Business. Please advise! Thank you.
Sadly the button is not included in 365 for Business . However you can still use the code =SharePoint.Contents or use Power BI desktop ( free ) and then copy the query to Excel
How about if my excel doesn’t have sharepoint folder in my excel function.
Notes on that in the description
Very helpful! Can you explain how to combine Source and Navigation in one step and add to parameter as source
Thanks, you’d need to go into the advanced editor for that and combine the 2 steps into 1 and replace the path with a word like SourcePath, then create a parameter ( via parameter button ) called SourcePath
Hi,
my date in the CSV Files (5 inside the sharepoint folder I connected w PowerBi) isn´t uploading fully. what did I potentially do wrong?
Hi, I’d suggest posting some screenshots and detailed description to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589
Or
community.powerbi.com/t5/Power-Query/bd-p/power-bi-services
Thank you so much for the detailed tutorial! Your explanation makes it easy to understand!
You’re welcome
I'm not sure what I'm doing wrong, but after I change the code to "SharePoint.Contents" and click on the "Table" link that I want, the "Content" header is just a drop-down menu. It does not have the two arrows to expand the contents. Any advice? Thanks!
Potentially a different table for that first click. It might be Shared Documents or could be something else
Freaking Awesome! Thank you. As a beginner this is extremely helpful
You’re welcome
This video helped me save many hours of waiting! Thanks!
You’re welcome Ida. Thanks for letting me know it helped
Hi, I have a question. If we have two or more root folders from which the data needs to be combined, how do we need to go for it?? Can you please explain the procedure for the same. Thanks!
This video may help :
Easily enter a File Path to Combine Files from SharePoint folder including sub folder option
ua-cam.com/video/mgVnk4R79ac/v-deo.html
Great..., this is what i'm looking for, Thank you
Glad to help
This is so nice. My problem however is I am using the dynamically created path - each day the Power Query has to look to different location. The link is generated in excel and passed to power query. Power query then filters the folder. How I would get to the certain folder using SharePoint.Contents please?
I don’t really understand sorry. What determines the folder change?
Great!
Thanks Virginia
Awesome contribution. you saved my tons of hours. Thank you soo much
I appreciate you taking the time to let me know you found it useful
Man!! Lifesaver - I was using previous method - in our company we have a preservation hold folder that is bloated, so it was taking forever to load a file from one of the other folders because it has to first load all data from the other folders before I can filter for what I want
Excellent!
Great video!! But what if each file has multiple sheets and I only want to combine a specific sheet from each file based on sheet name. Kind of like when you go the SharePoint.files route and use excel.workbook then filter based on sheet name.
One of the first steps is to pick the sheet you want to consolidate
Brilliant, just brilliant thanks so much 👍🏽👍🏽
☺️. Thank you
Awesome!! Thanks!
Cheers!
Save my life. First time using sharepoint folder.
Excellent .. glad to help Felipe