Hi, in this video we're gonna have a look at how to merge two tables together within the merge queries option in Power BI. Now, in the real world, you might have different tables in different data sets, and you might want to combine them, you might want to join them together, and there are two ways of doing this within Power BI. You can either append or you can merge two lists. Now, if we think of appending two lists, if the headings are the same in Power BI, or in the data sources, you can-, or in the two tables, you can actually combine or append the two tables one on top of the other, so you get a longer list. However, if you've got two tables which have got different headings, but show related data, then you might want to merge them together. And you can almost create a wider list when you join them together side by side. So, this is what we'll show you how to do in, within Power BI. We will show you how to merge two lists together.

Now, how would you normally do that, you know, if we weren't using Power BI, you might use Excel, you might do a bit of copy and pasting, you might, kind of, use some formulas to join or get data from another table into a single table from another data source. There's many different ways that you could actually use but Power BI lets you do it a lot simpler within in built functionality. So, let's see how we can do this. Now, what you probably want to do is start off with a new Power BI file, so open up Power BI and save it as a-, give it a file name and save it as a .PBIX file format, that is your standard, kind of, Power BI file format, .PBIX format. So, once you've done that, once you've opened Power BI, you need to bring the data sources into Power BI, and we are gonna use a couple of CSV files with tables in, that we're gonna connect together.

So, the first thing I want to do is go to the home tab, go to get data and I'm gonna go down to text CSV. I could also go down to more, to go to different data sources, the text CSV file format is also available in the dialogue box as well. So, just gonna wait for that dialogue box to come up, and then it should allow me to select the text CSV. The text CSV file format is at the top because it is quite a common format, so I'm gonna select that, I'm gonna click on connect. Now, I'm gonna go ahead and select the file format that I'm working with, so I'm gonna-, the first table is 2020 customers, I'm gonna select that, I'm gonna click on open, and again, it does take a little while for the files to connect, but then the navigator dialogue box will come up. Now, the text, if you connect to a text CSV file format it does look at little bit different to how you might connect to another file format, such as Excel or Access database or any kind of database. What you see is the table come through, and one of the biggest tips I can give is, is the fact that when you connect to a CSV file, sometimes it loses the headings at the top, and you can see the headings have, have been included as the second row of data. So, if you see this, we can actually fix this within Power Query Editor. Now, in the bottom right-hand corner, we have got our normal, kind of, load buttons, so I can load this straight into Power BI, and manipulate the data source. But if I click on transform data, that will allow me to go in and manipulate the data source and model the data source and before we load it into Power BI. So, that's what I'd like to do, I'm gonna click on transform data and that will open up another window and as you know with power Power Query Editor it gives us an extra set of tools available to us within Power BI to model our data.

But the first thing I would like to do is to promote my, kind of, headers and make the first row of data there as my headers. So, all you need to do, great tip this, if you go up to the home tab, on the right-hand side there is a button which says use first row as headers. And if you click on that, that will just promote the headers to the top. So, if you are ever connecting to a CC file, you may have to do that, just promote the headers there. So, we've got the data in there, now if I want to merge that with a second table, I need to load that table into Power BI as well. So, again, I don't need to close Power Query Editor, and, and, and go through the steps again, I can actually bring in data sources whilst I'm in the Power Query Editor. So, all I need to do is go to the home tab, go to the new source, and select text CSV. And this time I'm gonna choose the second table, 2020 orders, and click on open and that should load the table in. So, I end up with 2020 customers and I should end up with 2020 orders as well. Now, I do get the navigator option here, you can see that the headers for this are okay, so I can actually just click on ‘OK’ and it will load the table in. I don't need to promote the first row as headers, but then I've got my two, kind of, queries there. And any tables that you wanna merge together, you need to load into Power BI. So, once we've done this, I would always select the first table, 2020 customer orders, to start with and you see that it gives me a preview on the right there. And if I want to merge these tables together, I can go up to the home tab and on the right-hand side I've got the merge queries button. Now, we talked about the append queries, which was our, kind of, longer list, that button is just below, and we'll talk about that later on, but if I go to the merge queries, I can merge a query into the 2020 customers table.

So, I can have the second table merge into 2020 customers, but I can also merge the queries as new, so I can leave the original queries as they are, the original tables as they are, and then merge the two tables into a separate query. That's what I'm gonna do, I'm gonna go merge queries as new. And that will bring up a dialogue box, and at the top it's automatically picked up my first table that I was on, 2020 customers. In the second dropdown, I need to click and select the second table. And there we are. Now, when we perform a merge, what it actually does is it actually combines the table based on a common field. So, a common field in both of the tables is customer ID. So, in here I've got customer ID, so I need to select that from the customers table. Now, the name of the table doesn't, or the field doesn't really matter. In the second table it's called customer reference. They are the-, kind of, the same data set. So, the headings don't really matter, the data set has to be the same. So, we're using those two fields to connect the data sources together, and we've got the default join kind, we'll talk about relationships in another module, but if I click on ’OK’, that should create a, a combined version of customers and orders on the left-hand side, and it calls it merge one. Another great tip is you might want to rename that. So, on the right-hand side, you can, kind of, click and you can get rid of merge one and you can give it a better name. So, I'm just gonna call it combined, okay? And what you'll see initially are the fields from the 2020 customers, but if I use my scroll bar at the bottom and I scroll across, right to the end, I get an extra field here which says 2020 orders, and I get a little gear button on the right. And if I would like to see the fields from the, the second table, I just simply click, and I can select the fields I'd like to display.

I'm gonna select-, get them selected and display all of them and I'm gonna click on ’OK’. And there we are, so now as I scroll across, you can see all the fields from the second table, and I've got all of these fields from the first table and the second table in one view. I can continue to further manipulate the fields, but if I'm happy with that, I can simply just click on close and apply in the top left-hand corner. So, if I do that, that will close the Power Query Editor and it will load the, the queries into my Power BI. And again, it takes a little while, but once they're loaded in, you'll see the, the initial two tables that we loaded in, the data sources, the 2020 customers, the 2020 orders, but also the 2020, or the combined version as well. So, that seems to have just about finished there, so you can see on the fields plane on the right-hand side, I've got my 2020 customers, 2020 orders and then my combined version. So, I could utilise that, that now in a report if I wished to. So, that's how we merge two lists together in Power BI.

Merge queries

About