Combining Data Tables with Append Queries in Power BI

While append queries might sound very similar to merge queries, they're doing fundamentally very different things. In merge queries, which we saw earlier, that is about adding columns. We're taking columns, which are kinds of data, from one table and adding it to another table.

So we're getting more kinds of data in a single table. When you're doing append queries, this is adding rows. So you've got multiple tables, and you have the same kinds of data across all of those tables.

They're the same columns, the same number of columns in the same order, and you have rows that are in one table, and you want to add them as additional rows in another table. So you're essentially putting those two tables together, kind of like a data sandwich, if you will. So let's see how this works in Power BI.

To understand why we need to do this, let's look at the Sales by City Excel file. In here, we have multiple worksheets, and all of them have the same number of columns, all in the same order, with the same kind of data, but the actual entries are different because these are for Boston, these are for Chicago, and so on. Now we do have a location column, and what I want in Power BI is to have months, location, and then whatever these different categories are for different sales, or whatever it is that we're counting, doesn't really matter, but we alphabetize those just so we can see that it's the same columns in the same order, so you can easily identify that.

I want to just see these columns once. I want months, I want location, and I want location to contain all of my locations. What I don't want is a Boston table with a location inside of it.

Separately, a Chicago table with a location inside of it. No, I want one single table with all the data for all my locations. So I need to append the rows, essentially taking rows from here, copying them, and coming down here, and pasting them without having to actually do that.

So let's see how we can do that in Power BI. So let's remember that this is an Excel file that we want to bring in, and in a new Power BI report, which we can go here and create a new blank report. If you already have a file open, go click on file, and then new blank report here, and here I can bring in that Excel file.

Sales by city. Now we know that we want to transform the data because it's not in the format that we need. I want to bring in all four worksheets, so I'm going to check them all on, and I'm going to click on transform data.

Don't worry if this says refresh for me, it's just because I've done this file before, and so if you have brought in the same file, it's just saying that this preview might be old. It hasn't refreshed the preview in the last 30 days. So you won't get this message here, and it doesn't really matter for what I'm doing here either.

So what I want to do is take all of these four different tables, or different queries, and put them all together into one new table. I could technically add the rows from these other tables into this one, but I think the name of it would be weird, and even though I could rename that, if I ever want to go back and look at this and maybe add a new location, I don't think that the history of this file would be very clear if I renamed Atlanta to, let's say, sales. So I want to create a new table by appending all of these into that new table.

When I come up here to append, I don't want to just click the button here; I want to click the little arrow next to it, because I want to append those queries as a new table or a new query. And this will allow me to take these three or more, in this case, tables, and put them all together. And I'm going to double-click to add them over here.

If you want to remove one, you can select it, and if you scroll down here, I don't know why they make a scroll in this tiny little dialog here, but you can remove that if you need to. You can also select these and rearrange them, moving them up and down if you want to control the order of those rows, if that matters, but in our case, it doesn't really matter. So I'm just going to make sure that all four of them are over here before hitting OK.

So this now creates a brand new table, which is combining all of these into this. I'm going to double-click on that name and call this sales by city, or I could just call it sales if I want to. And I can see that I have Atlanta, and I do have a null row.

There must have been an empty row at the bottom of one of those, well, in this case, the Atlanta there. But then I have Boston, and I have Chicago. And so what was important about this setup is that the first column is months, then the next one is location, that I have the same number of columns in the same order, and that when it's combining these, when it's appending them, that it works properly.

It's not going to match up things if they're in different orders. It's just going to take everything in the first column, everything in the second column, and kind of put those together. Now, in this particular dataset, when I have a row, I do not expect any empties to be there anywhere in this particular dataset.

So if I have an empty here, I would want to remove it. So I'm going to come up here, hover over this little green bar there, which I can see a little bit of gray, which indicates that there are some empty rows. And I'm going to go ahead and remove those empty rows.

Doing it here after the step of appending them means that regardless of which file might have those empty rows, because they're all put together and it has appended those, and then it does the filtering, it will filter any number of those empty rows in this final result set here. So I am now done. I'm going to click close and apply.

And instead of getting four separate queries here, I just want to see the sales. Now, notice it's loading all of those in. I didn't do anything to disable the loading of those separate table zones.

So what we're going to see over here is we're going to see all of them load, including sales by city. This is not what I want because I just want the sales by city with the location inside of it. I don't want to see all of those other separate ones, but I wanted you to see this.

This is actually why we do the append, because if we leave them as Atlanta with location inside of it, it's like these are just the sales for Atlanta. These are just the sales for Boston. I want all of the sales, with location being one of the things in there.

So I need to disable the loading of those things. So I'm going to go back into transform data by clicking on the icon here to go back into Power Query. And I do not want to load those four into report view.

I'm going to right-click, and I can't delete these because they do need to be loaded so that they can be appended into sales by city. So I am going to simply turn off the enable load. I don't want to load those into report view.

When it goes italic, that indicates that it is not being loaded into report view. And I wish I could hold shift, click on multiples of these, right-click, and turn the loading off on all of them, but no, I've got to go one by one. So I'm going to go here, turn off loading, and go here and turn off loading as well.

So this way, the only thing that will be loaded into my report view is this combined table, this appended table. Those are being used to create it, but they don't need to be loaded into my report view. So I'll close and apply.

We'll see that now we will only have the one sales by city. And if I create a visual here, let's say I create a column chart for the sales based on location. Here we can see that I've got my sales for all four locations.

And that is what I wanted. I wanted to be able to aggregate all my sales, but be able to segment it by location. That's why we needed to do the append in this case.