Power BI: How to Combine Multiple Excel Files from a Folder

hello friends in this video we're going to take a look at how we can combine Excel files from a folder using power bi [Music] we have already seen how to combine CSV files but let's dive a little deeper into how do we work with Excel files the scenario here is we have these multiple files let's say by Department and the first one we can tackle is we're going to tackle getting the product detail sheet from each of these files so they have accessories product detail bikes and clothing product details so let's head over to power bi desktop and we're going to go get data more and I just go to file and folder hit connect we're going to point to our folder learn power bi hit OK now gives me this option I have a few options here I can say combine and edit combined and load or just click load which will the hood the table as is if that's what you look for or edit which would lend land you in the editing pane with these files so for now I'm going to say combine and edit and it brings me the screen which says combine files and it's saying hey select the object you want to be extracted from each file so at least in this scenario we can examine the different files and for our scenario we're going to just select an example file and say product detail so that's the element I want to be extracted from each file now when I hit OK is going to do all the heavy lifting for me so we're back and notice my query is ready it went ahead and grabbed the you know the the product details sheet from each of those and this is the end result so all the hard work is done but you notice that many it created quite a slew of queries well that's okay now it keeps the source name the file name here you can delete it if you don't want it but that's our table you can hit just hit close and apply and we're going to end up with our bar bi-product table let me rename it really quick the first Center is very clean but let's imagine a scenario where we need to combine the budget summary file so if you go to the file so you can see by the summary it is slightly more cluttered we need more transformations and this time we need to grab the budget summary file from each of these excel files let's see how we can do that we're going to go back to bar bi desktop again get data more file folder hit ok same folder as before and we're going to click combine and edit again and asked them to choose the element to be extracted and this time I'm looking for budget summary so I'll select that hit OK and it's done so I'll say budget summary great but unlike product which was very clean and it did everything right and this table was ready to load if you look at budget summary it's full of gunk right and I got all those header rows and all those files they've been combined so we don't we'll quite want that so at this point you have two options one option is that you do your cleanup in the combined file but if you're willing to dive into the innards of the structure that was a scaffolding that was created for you there's an easier way which is which is actually going to work better which is you instruct power bi or teach power bi how to process each file before it's combined in this manner so what's happening here is that it's the key step here is where it invokes this custom function right and it's invoking this this function right here this one now if I were to go over and look at that function and click advanced editor it's going to give me a warning and what this warning is saying is that this function is tied to this query like Siamese twins whatever change you make to this one it's going to be reflected in this function however if you were to ignore this warning then that link would be broken right so that's how Barbie I set up so again so we can use this to advantage we can go to this sample file sample transformation and build a set of transformation which we want to be applied on each tab each budget summary tab before it's full then so let's step through those really quick remove top rows similar to what we have seen earlier and say does that contain great news first choice headers move all right so we're done with these and all of these changes are reflected in our files so if you go to our budget summary table there's an error but this is pretty meaningless it's only happening on the changed I've SAP change type step we can easily fix that if you go back to our custom function that's where the custom function is invoked and then if you look at when the results are expanded then you see that it's a very clean table because all the steps that we apply to the sample file are now being invoked using the function on each of the of the sheets here so that's it I can you can't ignore the change type because I've already handled change type in the function so that's it so either way if you have a clean way clean file combining that is easy and in that case you can pretty much ignore all of these underlying functions but if you have a complex scenario and you can combine it and it doesn't you the results that you need then you can go in to the sample file you'll notice you can tell that by the icon here so you don't want to be editing the function the function here with the FX icon in fact as you saw earlier it's going to give you a warning if you try to do that so the way to do it is whatever changes transformation you apply to this it's going to be applied mirrored in the function and in turn it's going to be applied to each of the elements which are being extracted and combined so another question that I've been asked is how do I combine files but retain the file name as you notice here in both of these the file name is retained so it has source name and the budget summary has the source name as well so if you follow this process you're going to have the file name and later maybe you can use that as part of your data set if you need

Channel: Avi Singh - PowerBIPro

Views: 86,941

Rating: 4.810585 out of 5

Keywords: Power BI, Power Pivot, Microsoft Excel, Avi Singh, AviSingh, PowerBIPro

Id: 9sfCDCpWTfc

Length: 8min 14sec (494 seconds)

Published: Sun Apr 23 2017