COMBINE EXCEL SHEETS in POWER BI // Customised Transformations // POWER BI / EXCEL / POWER QUERY

In this video, I'm going to show you how you can combine sheets of data that follows the same format from the same file using Power Query in PowerBI. I'm going to show you how to combine these sheets with different columns and also do it dynamically so that new sheets can be added, but the transform will still work. All of that and more. So, without further ado, let's jump in. Combining files that follow the same structure is probably one of the most common features that I use in Power Query. It allows me to consolidate multiple files of data into one query drastically simplifying the semantic model uh once I start reporting on it. The typical scenario that I get is usually something like this. I have monthly sales data in multiple files, one for each month. And if you just pull this individual monthly files into PowerBI, it will be a challenging task to do time intelligence calculations without actually combining them into one. Power Query, whether in PowerBI desktop or in Excel, allows you to do transformation that allows you to basically combine these into one query. And they make this process simple and pretty much automated. Let me show you. So from PowerBI desktop, we'll hit get data and more. And then from here, we'll select folder. We hit connect. Here's the file path for the folder with all of the files that I've just shown you. I'll hit okay here. And from here you can simply do combine and transform or combine and load which will essentially do exactly what's what you imagine. I'm just going to select the first sheet here as the sample file. And once we hit okay it will open up the power query editor here which will essentially have combined all of those multiple files into this one query monthly sales. It's created a bunch of other kind of helper files here that does all of the iteration to get to this monthly sales. But using this as it is, you didn't really need to worry about all that detail. You simply just selected combine. And as long as all of your files follow the same structure, your output file here should be exactly as you expected. And by the way, if you missed that uh prompt to combine your files at the beginning, you can still trigger it from here. Uh like for example if we go back to source here you can actually just press this button combine files which will do exactly the same thing. It will create all these helper files let you select the sample file to do the transformation and it will generate all of these steps and the helper files for you. However with one of my recent clients I actually encountered something a little bit different. So this is the file that they have. They have one file called sales and in this file there are multiple sheets of data. Each sheet is for a company itself. And for each of these companies, they have a list of all the different sales for the different categories of products that they sell. One column for each day of that month. So I thought great, let me just use the same functionality that I just showed you just now. I just simply do the combination. So from the Power Query editor, we'll go to get uh data from Excel workbook this time. And from here, you can see that we don't have the same options as before, but okay, for now, let's just pick one of these sheets. Let's pick barbec for example. And Power Query's already created a bunch of steps here uh for us. But we're going to actually delete all of these. So, we're going to go to the step navigation, delete until the end, and we'll just keep it at the source. And normally, like before, from where the data table is, you simply have this option to combine. But here, in this case, we don't actually have that option. and we have something called expand instead of combine. So we don't actually have that simplified way to do the combination like we do with combining files. But also there's another problem with our setup. Even if we can do the combine files method here with the sheets, we still have another problem to resolve. The column headers for each of the files are dynamic, which means that it represents the dates. So they're not always the same across all the different sheets. So, we need to find a way to transform all of these the same way across all of these other companies, and we're going to do exactly that in this video. Now, here's the trick. The combine files method creates us these helper files automatically. Now, I'm going to show you how you can create all of these manually so that you understand what each of these helper files do and that we can apply these for all of these sheets. So to keep things simple, I've deleted everything now in our uh kind of power query except that one sheet that we brought in from the sales which is the barbec uh basically all of the steps deleted except the source. This just gives us the list of the sheets that exist in that file. And each of these tables if you select them just gives you the tabled information for each of these sheets. What we're going to do first of all is just rename this to source

sheets. From here, we're going to create a parameter. So, under manage parameters, new parameter, we're going to call this one param sales. And then the current value, we're just going to add one there. And I want that one there just because we want to use it as a reference. So, from here, we're going to select advanced editor. And just before the meta where one is, we're going to replace this with a reference to the data column that exists in our source sheets. So we're simply going to go to source sheets, which is the name that we've given it. And then we're going to reference it with the box brackets, the data column. And then we're going to simply reference the first item in that array, which is just going to be the same sheet. If we hit done here, now this parameter references the data in the source sheet which if you pull this into a new query will just show you the first item in that array which in this case in our source sheet will be the barban because this is in the index zero. So the first um data column or the column row. Uh so we can actually preview that by just going to new source blank query. And in here we're just going to simply reference param sales. And if we select that you'll see that it simply brings in the data from our barbacon table. Power query has already created some uh some steps for us here. So we're just going to delete both of these. And you can see that uh we have the barbacon sales data which is one of the headers uh here to ex to show us basically from which sheet this is coming from. We're going to rename this to transform sales and uh we're going to disable this load because it's just one of the helper files. We don't actually want to uh reference this uh outside like when we load this. So we're going to disable that. And actually let's also disable this source sheets. I believe we don't need to use that. We're going to come back to these later. So now we need to create the function to call this transform sales. So we're going to rightclick this and create a function. We're going to call this function FX combine sales. And we're going to hit okay. Now what it's going to do is automatically create a folder that groups all of these items into one. And that's simply because they're all related to each other and just makes it easier to uh to organize them. Now we can go back to transform sales because this query is actually the one responsible for transforming all of the sheets that we will be calling with the function. So it is important that all the transformations that we apply here will be applicable to all the other sheets. So for example, if you have any references to uh columns that don't exist on the other sheets, it will cause some errors. So you might have some missing columns or some unexpected errors. So it's really important that all of the transformation steps here will be applicable to all of the sheets that you have in uh your file. So we use the barbcan as our sample file here. And uh already from here we know that we have to do a couple of steps from this to clean this up. And uh what we need to do first of all is just remove these first few rows. So the first two rows we're going to do remove top two rows. And then we're also going to promote the first row as headers. Power query created us the change step here which references the column names themselves here. uh which we know will be difference for each of the uh the sheets and we actually don't want any steps that reference any fields because uh these transform again will be used across all the sheets. So uh we don't want to use that. We actually want to delete that. And from here we want to unpivot from the category. So from the category header we just rightclick and click unpivot other columns. And there you go. So it unpivots all of those different categories for the different dates and the values within them. And we did all of these transformations without referencing any specific columns that will kind of not exist on the other sheets. So if you look at all of these steps, any of these steps don't really reference anything apart from obviously the unpivot other columns uh which uh it does just create this attribute and value which uh should work across all of them. And that's most of the leg work done. So now all we need to do is simply just put the source sheets in here just for simplicity sake. Uh we can right click on this and reference to create our uh combined file. So we're just going to

minimize that. We're going to call this one uh sales. And now we're back to the beginning. So now we just have the list of all the sheets from our file. And from here all we need to do is simply invoke a function. Invoke custom function. We're going to select our combine files function and reference the data column. We're just going to call this one uh sales. Hit okay. And now that they are tables, we should be able to now uh expand the category attribute and value. So we're just going to deselect original column here. And uh there you go. So if we just select the fields that we want. So the name, category, attribute, and value. Right click, remove other columns. There you go. So, we have the dates and the amount here. You now have all of those multiple sheets combined into one using Power Query. And that's really it for this video. I hope you found this video useful and not too long. Hopefully, in a way, it is helpful to know what these do because it means that now you have the flexibility to uh modify these transform files so that you can have some custom transformations before all of these files get combined into one. — Hey, hey.