All language subtitles for 12. Merging Queries in Power BI Desktop

All right next on the agenda we have merging queries.

So you see the merge queries option in the Home tab of the query editor and when you click that button

it'll open up a dialog box like this where you can select two tables and matching columns in order to

create a merged table.

And basically this works just like a V look up works in Excel.

You're joining information from two different tables based on some common column or key.

So in this example that we're looking at here we're merging a table called A.W. sales data with another

table called A.W. product look up.

Both of which contain a matching product key column.

So essentially what we'll be doing here is grabbing information or attributes about those product keys

from the product lookup table and pulling the men or betting them into our sales table by matching them

to the associated product keys.

So my trick for remembering this is that merging adds columns to an existing table you're not adding

any rows you're pulling in more information and making your table wider by adding columns.

So very important point here just because you can merge tables and because it's an interesting tool

to use doesn't mean that you should in fact.

Generally speaking it's better to keep tables separate and define relationships between them within

a data model.

So more on that later.

But keep that idea in the back of your head that you can merge queries like this.

But it's not always the most efficient solution.

So why don't we go ahead and jump into our query editor and I'll show you what one of these merges actually

looks like.

OK.

So I'm in the query editor of my Adventure Works report right where we left off.

I've got my product table customers calendar and my sales 20:17 data now to demo the idea of merging.

Let's say that we want to look at our sales data you know which has our quantities and our keys for

territory customer and product and let's say I want to pull a little bit of information about these

products right here and to my sales table without having to look at a product key and find that associated

product key here in the product lookup table and get my information that way.

Let's say I just want that information accessible in one place.

I want to pull those columns right in there to the sales 20:17 table.

That's a case where you could use a tool like merge.

So I'm going to start by selecting the sales table.

I'm in my Home tab all the way on the right you'll see the merge and append options we're in it talk

about append next.

But within merge I can do it two different ways.

It can merge or merge as new.

And if I just simply merge queries it will preserve the exact tables that I have in place now.

It will just add the merged columns into my existing A.W. sales 20:17 table if I choose the second option.

It's going to create a brand new merged table that contains the merged result while maintaining my original

component tables as well.

So I don't really need or want that option at this point so I'm going to go with the first option just

merge queries and what I see here is all right I've got my first table the one I've selected already

populated here on the top half of the screen and now it just says select a table and the matching columns

to create this merge so I can choose from one of my other queries or tables here.

In this case let's go with A.W. product Look-Up gives me a little preview here as well.

And now the only column that actually matches here if I look through these is that product key column.

So I'm going to go ahead and select product key in the product table selected in the sales data and

I get this nice green check mark that says it's matched twenty nine thousand 481 out of the first twenty

nine thousand four hundred eighty one rows.

So you've got a perfect match here which is always good to see.

So don't worry about the join kinds here.

There are a few different options different types that you can choose from the default left outer is

typically the one you want.

It's basically saying take all the product keys that exist in my first table and grab any rows that

find a match.

From my second table and for our purposes that will do the trick.

So it's OK.

And you see what happens here it added a column but the column looks kind of funny it says table and

it's formatted with these links.

So there's one extra step we have to take here which is to click this button here.

That actually expands the table out and converts it into the actual columns that I want.

So by default it selects all of the columns from the product table.

If I press OK it would pull in all 10 or 12 of these columns into my sales table.

In this case I don't want all of them let's just pull in the product key so that we can make sure we

have a match and maybe the product name and the color.

And this last check box here just determines what the name of the column is going to look like.

And if I leave this selected it's going to create a prefix for every column with the name of the product

table which for the sake of this example that's fine.

So when you press OK it basically expands that temporary table column into the three columns that I

had selected from the list the product key.

And again it's got that prefix of the table name the product name and the product color.

So if we scroll to our original product key is got product 5:29 2:14 scrolling to the right 5:29 2:14

you can see that it matched.

And now we have this new information that hadn't existed in the sales table up to this point about the

product name and about the product color and it's populated for every single row in our table just like

a V Look-Up would have in Excel.

So like I hinted at earlier just because we can do things like this and merge or tables together doesn't

mean we always should in fact merging information in the way that we just did actually creates a lot

of duplicative and redundant data which can be avoided entirely using table relationships instead.

So again we're getting there soon.

But for now let's go ahead and delete that merge that we just did.

And we're back to where we started.

So there's a quick demo for how to merge queries.