The best way to connect to a SharePoint Folder to speed up your Excel and Power BI data refresh

how to consolidate a folder of files from sharepoint and what's the quickest method to do it let's go here's the three files that i want to consolidate in my excel file or my power bi file i'm going to use excel but it's the same concept and techniques in excel how do you do it well the first thing you need is the right path and that is the main route folder which in this case is this sharepoint demo so up until that part of the url okay that's the bit you need nothing more than that so i'm going to copy that path i'm going to go into excel i'm going to connect to that sharepoint folder so get data from file from sharepoint folder in power bi you just click the big get data button and sharepoint folders in the list it'll ask you for the for the path to start with and if you hover over this little i it tells you the root path not including subfolders it really is a pain if you accidentally include them so there we go we've got the path i've left the slash on the end i never remember whether you meant to or not but let's give it a go click ok and the very first time you do this you'll have to sign in to your microsoft account sign in it should then remember it on this computer after that okay i've jumped through all my sign-in hurdles and my multi-factor authentication and everything else and now i'm going to click connect now the problem with this is that every single file in your sharepoint folder gets listed so you can't simply go combine and transform in fact this is a totally pointless button in my view it's really you know this is everything in your sharepoint site that you've got access to which is crazy um within that sort of root folder um okay so what you have to do well i'm going to click for the transform data button this is a list of everything and now the challenge is to go and find the folder you want now let me start to show you the method and i'll show you a better method so this is the the sort of the terrible method is you've got to try and find the folder you want and filter on this list in fact a little trick you can't make this column wider but you can do this right click move to beginning make it wider okay and if you don't want that step then fine just delete the step and you'll notice that the column is actually wider what a weird hack okay so there we go we've got that little field here and i can filter down and there's ways of navigating through to this and then you've got to find the folder and then once you find the folder you want you've then come across here and you click this little double headed arrow okay so that's one way of doing it but i think a better way is probably the contents way so the sharepoint contents so a better way you change this word files to dot contents okay press enter and you're given this sort of view now the thing you want to do here is you want to drill down into the various levels so if i go to the shared documents and i click on table it then lists all the subfolders and the what subfolder i want is this youtube demo let me just bring this back up so under documents youtube demo if i go back to documents there's a few different folders in here okay so if i go back here there's it is click on table and there's the files i want okay another next step to consolidate them is to click these like this icon but my recommendation stop there call this sharepoint folder and then reference it okay i'll show you why in a second right click reference it's now linked this one's now linked to this and we can expand this out click the little expand button and providing all the sheets are structured the same and have the same sheet name then this is pretty straightforward in this case every file just has one sheet called sheet1 so i click on that that's all the data there is you know just those items there click ok and it should then create this auto consolidation step i've got a whole nother video about how to consolidate files from a folder i'll put a link in the show notes below so you can check it out but ultimately we're done right this is the consolidation and then you just go close and load and every time you put a new file in there and click refresh or set up a scheduled refresh if you're using power bi this data will get updated okay so check out my video as well about explaining all these steps it's the consolidation video i've done okay so the important thing is you know why did i use this sharepoint folder as a separate step well the nice thing is if the files move to a different folder for example i can just change this to something else potentially if it's a different subfolder and then the sample file which refers to the sharepoint folder and the consolidation file which refers to the sharepoint folder will both update rather than you having to go and mess about with separate little queries so i would do it that way the other little thing that little tip in here in terms of this um source step and the navigation step you see the navigation step jump straight to youtube from this root folder it doesn't seem like it's navigated down the various steps so what you do click on the advanced editor this little step isn't actually showing okay and if you drill down a few layers it doesn't show so what i tend to do is this just actually put a little thing there and call this something like source two equals source and then refer to source two okay and then click done and then it breaks out your steps so there's shared documents so you could even just rename this rather than source two you could call this the um main or high level high level folder something like that yeah then into shared documents then into youtube demo or root folder great name okay so there we go that's how i'd approach it and this sharepoint.contents method is much faster than doing the filtering for the folders using the sharepoint.files method that appears out of the box so go for sharepoint dot contents it's a better method it's one i'd recommend make sure you reference the query and then every time you just add a new file in you just click refresh and your report will update okay so close close and load two let me load this to a table for example in excel here we go there's my data the only thing to be aware of is the sharepoint in excel the sharepoint folder gets loaded as well so right click load to only create connection okay there we go hope you find that useful let me know what you think in terms of consolidating historical data i'll do a video coming up soon about using data flows to actually consolidate your historical data now you can't yet use data flows with excel it'll be coming soon hopefully but with power bi you can so stay tuned i'll put links in the notes below to that video once i've done it okay catch you later [Music] you

Channel: Access Analytic

Views: 133,753

Keywords:

Id: -XE7HEZbQiY

Length: 8min 44sec (524 seconds)

Published: Sun Mar 27 2022