Power BI - Combining Multiple Sheets From Multiple Files in Power Query

hey everyone read here from the havens consulting YouTube channel for those not familiar with me I'm a Microsoft MVP with power bi just like Parker and I'm collaborating with him again for another guest post between our channels today I'd like to walk you through an advanced folder combined technique more specifically what happens when you need to combine multiple files but when each file is an excel file that has multiple sheets you want to combine as well to top off this demonstration we'll even get to see how to clean each sheet individually so let's get started so I'm going to start by coming up to the get Data button up here go to more and I'm going to select folder as my data source right here and hit connect and go ahead and browse and what I've done is I put this on my C Drive I have a folder in here in this case data files that contains multiple files that I want to combine select ok go ahead and select ok again there AR and we have two files in here so let's go ahead and hit combine and edit and as I mentioned what you can see is that each file does have a sheet for Canada and a sheet to pre United States we could even see that if I actually go to the 2015 file up here they have those two same with the 2016 1 so these files all contain the same multiple sheets but they have multiple sheets in them so how do I apply it transformation that cleans these up and combines them as well as the files so kind of two levels of combinations that need to happen so what we're gonna do instead of selecting an individual sheet I'm gonna right click on the sample file parameter folder that you see up here right click and select edit and you can see that that created that final combined table over here on the left but we're not going to focus on that yet we're actually going to come up to the transform sample file from data and you can open or everything else in here except for just this one transform sample file that's where all of your transformations on each individual file or sheet can be made so we're going to select that and what you can see is this is the sample file that we set up in that popup menu earlier and any transformations I do on here will be applied on each file individually before it gets combined to this final table down here that's appended together so we have both the United States and Canada so how do I apply transformations to each of these individually and let's take a quick look at the data if we actually expand this out there we go we can see that there is some Jeong data that is in here so United States looks like it has some extra header rows at the top it needs to promote the headers as well we also need to do that for Canada so there's definitely some information in here that we can clean up so how do we do that individually so to start with what I'm gonna do I'm going to create some sub queries in here that are gonna clean up each of those sheets individually in this applied steps list so to start with I'm going to actually right click source I'm going to say insert step after and what that does is simply reference the prior applied step in here so I'm going to call custom one I'm gonna rename that to source custom and I'm gonna use this to transform my United States sheet and then I'm gonna do that as well for Canada so I'm just going to go through my normal process now with this I'm going to select United States as a table there we go that pops it out now that Didier promoted headers and change types so it did a few things that I didn't want quite yet so I'm gonna delete my change type I'm gonna delete that promoted headers and what I can see here is that first row is something I can get rid of so 2015 sales that's a transformation I can go ahead and clean up so I'm going to select the query transformations up here select remove top rows and remove the top one there we are and then I'm gonna do the same thing at the bottom so if I actually take a look and we throw all the way down to the bottom of this query let's see if I can get it to load there's a grand totals road down here at the bottom and I want to get rid of that too so come up same thing select the query transformations remove bottom rows go ahead and select number one perfect so we've removed the dirty data out of here and now I can go ahead and use first row as headers up here pop those headers up there we go and I'm cleaning this up and for now we can actually get rid that change type step as well so now what I can actually do is I can do this same thing that I just did for United States sheet I can also do that for Canada so I'm going to right-click promote it headers I'm gonna select insert step after and up here where it actually is referencing the previous step promoted headers I'm actually gonna change that back to source custom there ar and that will now point back to that original step before I selected a sheet so I'm gonna do that there we are now I'm actually going to select Canada this time perfect and I can come up here I'm gonna go ahead and rename custom one and call that source Canada and I'm even going to change this one at the top from source custom resource United States there we go source Canada down here and we can get rid of those change types and that promoted headers and the same thing I can do to this one now that I have the Canada sheet I can also remove top rows because that has that top row at the top remove number one now if I take a look in column one they're actually in to hit a quick load more there we go there is no grand total row in here so this one actually does not have a grand total at the bottom so I can do a separate set of transformations on this so I don't actually need the bottom row like the United States dude I can just simply remove that top row and promote the headers perfect so I essentially now have my Canada transformations in my United States transformations do you want to make this really clear as well to make sure that you have clearly labeled where the end of your transformations are for each one of these as an example promoted headers for the United States transformation is technically the last step but you can again just insert a step after which doesn't do anything except for referencing the previous applied step and I can rename this too and I'll put a couple little dashes in their final US table couple dashes and the same thing as well down here inserts if after rename that custom one two couple dashes final Canada table there they are and now I can actually Union these two tables together so I'm going to come up to a pen and what we can do is you can just go ahead and select just transform sample file well edit this in a second and just kind of change the EM code a little bit so a pen in query you notice that it says final candidate table twice so all we have to do is change this first one to final US table there our final candidate table so it's appended those two together so we can even change that to appended u.s. and Canada there we go take a look over here we can see that United States and Canada is here so now we've cleaned up both individually and ended up with a final thing and our transform sample file so these steps that we have right here with the append is going to happen on every single file in that folder and then it's gonna come all the way down to this combined query at the very end that's appended all of these files together and you will notice that those an error just because the change type did attempt to do a change top' on a bunch of call names that no longer exists so we can quickly get rid of that but we can see a couple of things now our source in here is 2015 and 2016 sales so our folders combined all the files and then scroll in to the right we do have country for United States and for Canada as well so we have each of those sheets so it's a great way to do that and technically I could have done all the transformations and here after they combine all the sheets and everything else but I would have found it more difficult to do that so this is showing you a great way to combine those individually sheet by sheet and it does take a little time to set this up initially as you can see through some kind of nested queries in here but it's a nice way to be able to do that without having to use a whole bunch of different folder combines and then add it all those together so for me this is a simplified way of doing it so I hope you found this useful and if you like this video please click that like button below or go ahead and subscribe to Parker's channel for more great videos like this you're also welcome to hop over to my youtube channel as well and check out those videos and subscribe if you like and otherwise I will see you in our next video

Channel: BI Elite

Views: 36,524

Rating: 4.9306931 out of 5

Keywords: Power BI, Data Analysis, Data Visualization, Data Science, Business Intelligence, BI, Microsoft, Dashboard, Report, power query excel 2016, power query excel, power query excel 2013, power bi power query, power bi combine multiple excel files, power bi combine tables, power bi combine sheets, power query combine multiple excel files, power query combine multiple worksheets, power query combine rows, powerquery combine sheets

Id: GvKYUgka_4Y

Length: 8min 5sec (485 seconds)

Published: Tue Mar 12 2019