How to combine (and debug) Excel files From SharePoint Folder

one of my favorite features in power query is the ability to go to a SharePoint folder and consolidate all the files that are sitting in that folder so imagine putting all your Excel budget templates in a folder clicking refresh and it just pulls them in consolidates them and you've got one beautiful table of data it's awesome but if there's a problem in one of those files it can be really hard to work out which one's the problem file and your refresh fails so this little tutorial how to set up a list that Flags the problem files and avoid that error happening in the future okay let's go [Applause] from here in my in this case OneDrive but SharePoint same thing okay I'm in the folder and I want to consolidate all these files okay so this is what you do the first thing is you grab everything if you're in OneDrive you grab everything up to the end of your name okay don't grab anything more than that so Ctrl C and then I'm gonna go get data from file from SharePoint folder okay this works with power bi as well you know power query is the same in both so I'm going to paste it in here and click ok and then you always have to use your organizational or Microsoft account depending on which one pops up here if organizational account pops up use that okay sign in and then connect right the first thing is you cannot click combine you have to go transform okay this is the list of all your SharePoint OneDrive contents okay so you can't use that combine button and what you want to change here is this SharePoint dot files I really recommend you change it to dot contents now this method does rely on you having access to the whole path okay to do this but it's much nicer because you now get this sort of explorable list so you go to documents you click on table you can then go to the next table it's like drilling down to the folders okay and I'm going to go to my budget submissions so and it's much quicker okay to refresh as well if you use SharePoint dot contents right at this point I could just click these double headed arrows but again my tip is I'm going to call this my SharePoint folder or something like that and then right click reference it okay it's easier for making changes in the future if your SharePoint folder changes in terms of the path changes so do it that way okay and now this is going to be my sort of consolidation and I click these little double headed Arrow all right it'll pop up and say hey which sheet do you want to grab from each Excel file and I'll just say ah actually I want to grab the sheet called uh what did I call it data this is just the first file in the folder and it says hey that's got a sheet called Data in it click on it just have a quick look there we go there's my data and I click ok and away it goes and it should just consolidate all your data which is beautiful this does rely on every file lab in the same sort of headings and every file having a sheet called Data okay and then it'll just work beautifully in a second I'll show you how you handle it when things go wrong okay which is really the aim of this video so here we go Department e d c a h okay it's all brought in beautiful but actually I'd like to sort of unpivot the data before it's brought in so I'm going to go back to this transform sample file I'm going to right click on this column I'm going to say unpivot all the other columns all right and then I've got a nice column and I'll just call this month for example and that'll do and this errors out whenever you do that sort of thing and then generally it's because of the last step it says Hey the column Jan couldn't be found just delete this change type step and look there's my data okay so that's Department E I can scroll down there's Department D beautiful all ready to load into my report okay and I'll just change these column headings in fact I'll shortcut it by holding shift transform detect data type let's see if it gets it right text text whole number yeah I'm happy with that and then I'll go home and click close And load 2 and I would recommend you say connection only to start with click ok with power bi you can just click close And apply and it works beautifully but with this one I just want to load the consolidation so right click load two table and click ok and this is just perfect it's just going to suck all the data in load it in and I could have loaded it to the data model if I wanted to if I want to do pivot tables and things so here we go one thing I noticed when doing this demo was in my source file I accidentally named the column May twice so I've got it's not really important for the demo okay um right so that's working you know I can refresh right click refresh when I add a new file in it'll bring in the new data so let's let's give that a go okay so I'm going to go back into my um SharePoint or OneDrive here I'll go here and I'll go into my new files including one that's empty so there's nothing in that sheet and one that's got the wrong sheet name so the sheet isn't called Data okay so let's move these okay and I'll move them up into this heading here and then I'll if it's moving there we go and then I'll then move them from here there's probably a quicker way of doing this um I could have right clicked moved um into the budget submissions okay it's pretty cool that you can do that you didn't used to be able to do that so in my budget submissions now I should have if I refresh this screen I should have more files okay awesome so if I go back down to my report and I go right click refresh away it goes and hopefully he's working no and I get warnings like this data could be not be retrieved from the database it's not helpful I'm not even using a database okay check the check the database server or contact your DBA um hey Mike power query team that needs fixing all right that's just a not a good warning message okay so I'm going to click ok and I have to double click on here to go and edit this query or I could go up to query and say edit either way will take me into the query all right so but then how do you work out which file is the problem because when I try and refresh this if I come in here and go refresh preview all right it'll run in fact if I scroll down it's just got it doesn't even show you here it's just this error and that's not really helpful okay again if I click next to error the key didn't match any rows in the table uh it might be giving you a hint here right what do you do well this is what you do you go to the invoke custom function step right and you have a look across here to the right scroll all the way across and here you can see the errors okay right so what you could do then is essentially right click on this column and remove the errors okay so right click remove errors insert step beautiful and then at the end everything will work and we've got all our data in here okay all great but we don't know what the errors are so what do you do well you can actually duplicate this step so you can say hey let's just right click duplicate this all right and rather than removing the errors let's keep the errors so we'll go across the right here now this says remove rows with errors now if you can remember this you can actually change that to select rose with errors if you can't remember it's just under the Home tab and you can say keep rows keep errors so I'm just going to edit the formula here okay to select rows with errors you know you'll forget you'll try and write keep rows with errors and stuff like this we've got to go select rows with errors should wear away there we go that's the two errors and then all I really need I don't really need anything else so I'm going to right click on this step here and delete until end and delete I just need this column so right click remove other columns and this is my problem files okay so this should now work and this is my problem files so close and load close and load two and I should be able to pick a table in a worksheet let's go there click ok and there's my two problem files but everything else should work so if I just go data and refresh all this should all load I shouldn't get any warnings and magically it works okay so there you go now one more little thing just before you go okay just keep watching one one more little uh trick here all right I want to actually check I'm going to change the folder path and see the warning that pops up okay and just show you why I did that first little step so if I go back into here and let me change this for example okay let me rename actually let me rename this to right click rename okay budget submission okay so that's renamed so now when I go back to my Excel file and I click the refresh all button it's going to wear away and it says the key didn't match any rows in the table okay again can you please make some more helpful warning messages power query team please um right it just goes okay okay so I'm just going to go back into one of these queries and have a look okay so I go into my SharePoint folder and try and refresh the preview and this time it breaks okay an error occurred in the SharePoint folder query ah thank you power query team so here's the SharePoint folder query Let me refresh the preview of that one okay the okay uh name equals budget submission so it couldn't find it so where do I change budget submissions okay I think I can change it there but let me give you a little tip in here right with this sort of thing the SharePoint folder step go to the advanced editor after the source put the word split equals Source comma and then change the next step to split now this doesn't really make any sense okay but check out what happens because what I've got over here is just two steps source and navigation and all the subfolders that I've drilled through are sort of combined into one step I don't like that it makes debugging harder so click done and check this out there's my split okay I can instantly see that this is the one that needs changing so I can go here change this to budget submission press enter and then everything will work nicely so hope you find that useful let me know what you think do you come across these issues have you been stumped by them I hope this helps okay I'll catch you in the next video [Music] foreign

Channel: Access Analytic

Views: 31,443

Keywords:

Id: x2i0MRsE3RU

Length: 14min 17sec (857 seconds)

Published: Sat May 06 2023