The best way to connect to a SharePoint Folder to speed up your Excel and Power BI data refresh
HTML-код
- Опубликовано: 2 окт 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 RUclips 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
The solution is super elegant. Thank you so much !!!
You're welcome. Thanks for taking the time to comment.
Omg, finally found this! Agree, this is a genuine service to humanity
Hah 😆, thank you
Thank you for posting a better/quicker way to connect to sharepoint folder! It worked very well for me!
You’re welcome
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
I wish I could give a x100 thumbs up. So useful for my job. Thanks
Awesome!
Best video I found so far on RUclips
Thanks for letting me know 😀
Excellent video and clearly explained. Thank you
You’re welcome. Thanks for taking the time to leave a kind comment
I just want to say that your video has helped me. thank you
Thanks for taking the time to leave a kind comment Bea
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 ruclips.net/video/vPV67RLGoOg/видео.htmlsi=whWP7aSIWmeKw_4x
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
Awesome way to do it. Thanks so much for sharing it. This will be a great way for our team to share data easily.
Glad to help Dan
your video helps me save a lot of time.... thank you so much
Happy to help
Save my life. First time using sharepoint folder.
Excellent .. glad to help Felipe
thank you so much!!! I wish you get good & blessing karma because of your very useful knowledge!!
Thank you
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
Brilliant, just brilliant thanks so much 👍🏽👍🏽
☺️. Thank you
Freaking Awesome! Thank you. As a beginner this is extremely helpful
You’re welcome
Even in 2024 this Video still makes me say : you are the Master !!!
Cheers Juan!
Great video - well paced explanation.
Thanks
Excellent Wyn, I’ll be using this for sure 👍
Cheers Brad
Just awesome! Ultimate trick!
Cheers Thomas
Great video! Learned something new! Thank you!
Cheers for the support Cristian
Excellent demonstration 😅
Thank you for that
Amazing! This is what I want. You saved my lifee haha
Glad to have helped 😀
Great video ❤. Thank you.
You're welcome
Awesome! Thank you!
You’re welcome
Thank you mate!
You’re welcome Alex
Awesome- thanks a lot!
You’re welcome
Bless you
Brilliant!!!
Thanks 😀
You are the only person who came with a decent solution that really works. Thanks!
You're welcome
You sir, are a saint. 😉
😀 too kind. Thank you
DSV thanks you for your service, at least NL
Thanks, not sure what DSV or NL mean though ☺️
Very helpful video! thank you
You’re welcome
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 : ruclips.net/video/cPN24NK3_68/видео.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
This changes everything
Glad it helps
thank you
You’re welcome
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.
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.
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 😀
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 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 ruclips.net/video/sLW3NbeGDy8/видео.htmlsi=hYz_scSKDlcWAB5z
Thank you so much!
You’re welcome
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
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
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 ruclips.net/video/g4oZ0pOpn-4/видео.htmlsi=42QsVit7ieSVJdzo
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 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?
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
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 ruclips.net/video/sLW3NbeGDy8/видео.html
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
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 ruclips.net/video/mgVnk4R79ac/видео.htmlsi=uq_bCTMHiYoLD8Lf
This is awesome! Thank you so much, Wyn. Another great video. Going to try it out.
One question: if I specify that I want the consolidation to pick a particular named worksheet from each workbook, how can I identify how many of the files don’t have that worksheet in them? I think I need to go get set up with your Patreon account 😎
The removed other columns step in the Applied Steps panel ruclips.net/video/-XE7HEZbQiY/видео.html would show errors for all those that don't have the sheet name
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
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
How can I like this video many more times? Thank you for it!
😆Thank you!
Thanks for the video. It's informative and usefull.
I appreciate you taking the time to let me know you found it useful
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.
gracias!!
De nada
Thanks for all. Do you have by chance all Dax practice book or video for hand practice?
Check out Supercharge Power BI Https://pbi.guide/resources/
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
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. 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
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
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!!
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?
Hi , thanks for sharing . Would this work if we have milions of rows ? or is there a work around ?
Hi Tomy, maybe archive the older data ruclips.net/video/g4oZ0pOpn-4/видео.html. Other than that it's a case at looking at storing the data in a SQL database instead.
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
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.
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
Great Vid. So when u load say April and May, would the source update with a refresh?
Yes absolutely
Hello @Access Analytics, at the end of the video you talk about a managing historical data through data flow. Could you please provide the link of this video please ? Thank you
Here you go ruclips.net/video/g4oZ0pOpn-4/видео.html
Great
Thanks 😄
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
ur videos are so cool! QQ: how do I read an excel file in SharePoint using SSIS?
I’ve never used SSIS sorry
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
Thank you so much tha was so helpful i solve my problem , love you man
☺️ glad I could help 🤘🏼
Thankyou so much , you saved countless hours!!!!!
Great to hear! Thanks for taking the time to let me know
Useful videos but now days most organization have shift to Ms Teams. Is there anyway to integrate same as like sharepoint folder link using Ms Teams channel into Power Bi? I tried searching for Teams it does mention Connecting to a third-party service still under development.
The Teams files section is SharePoint. There’s a button “ open in SharePoint “ in the Teams files section - then use that site address.
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 👍
Thanks for solving puzzles for me. Love you man. You are great 👍
Too kind ☺️
I dont have the sharepoint option how can I fix that?
Create a blank query and add this code:
= SharePoint.Contents("YOURPATH/", [ApiVersion = 15])
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
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
ruclips.net/video/nPlrQUbEn4o/видео.html
@@AccessAnalytic must've skipped this part, thank you!
Hi Wynn. The SharePoint.Contents( ) approach works really well. For private channels in Teams, at first sight it looks as if SharePoint.Contents( ) does not return the private channel. That is because a private channel actually has a different SharePoint URL, so the first step Source will have to point to another URL.
Good pickup Nick
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.
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
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
Hello, Is there a way to list the files from a subfolder, and from it's sub-subfolders?
If you just want to list the file names then leave the query as SharePoint.Files and then filter for your folder path “begins with”
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
This video save my time and money. Many Thanks!
Glad to help 😀
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?
Can anyone that has sharepoint access can refresh the file?
Yes
thank you ! :D
You're welcome
Thank you, thank you, thank you for making this video, so incredibly helpful!
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
Worked like a charm. Thank you sir.
I appreciate you taking the time to let me know you found it useful