All language subtitles for 027 Merging Queries_en

Instructor: All right, up next we have Merging Queries.

So you'll see the Merge Query options here

in the Home tab of the Query Editor.

And when you click on that button,

it'll open up a dialogue box like this,

where you can select two tables with matching columns

in order to create a merged table.

And basically, this works just like a lookup 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 Sales Data

with another table called Product Lookup,

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 them in or embedding them into the 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.

So 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,

that doesn't mean that you should.

In fact, it's generally better to keep tables separate

and define relationships between them within the data model,

and again, more on that later,

but keep that idea in the back of your head,

that you can merge queries like this

but it's always not the most efficient solution.

So why don't we go and jump into the Query Editor,

and I'll show you

what one of these merges actually looks like.

Okay, so I'm in the Query Editor

of my AdventureWorks Report,

and to demo the idea of merging,

let's say that we wanna look at our sales data,

which has our order dates and order numbers,

our product key, customer key, territory key,

order line items, quantities, right?

We've got all of these attributes related to our sales.

And let's say we wanna pull in a little information

about these products right here into the Sales table

without having to go over into the Product Lookup,

find the associated product key,

and then look at the information here, right?

Let's just say we want all of that information accessible

in one place.

So this is a opportunity

that we could use a tool like Merge.

So I'm gonna start with the Sales Data here,

and from my Home tab,

and all the way over on the right here,

you're gonna see options for Merge and Append,

and we'll talk about Append next.

But with Merge, I can do it in a couple of different ways.

I can either Merge Queries or Merge Queries as New.

And if I just simply use the Merge Queries option,

it will preserve the exact tables that I have in place now

and it will just add the merged columns

into my existing Sales Data 2022 table.

If I choose the second option,

it's gonna create a brand new merged table

that contains the merged results

while maintaining my original component tables as well.

So I really don't want or need this option at this point

so we're gonna click on Merge Queries.

So what we have here is we have our first table, right?

We had selected the Sales Data 2022 table.

So this is the first table that shows up

in our Merge Queries option, right?

We can see all of the columns contained within that table.

And then below that we have this dropdown menu,

and this allows us to access all of the other queries

that have been added into the Query Editor.

And again, we wanna grab detail

from the Product Lookup table, right?

And so then again here we get this preview

of all of the columns contained

within our Product Lookup table.

And the only columns that these two tables have in common

is Product Key.

So if I select Product Key and Product Key,

Power BI goes through, you can see down at the bottom here,

it says, "The selection matches 29,481 of 29,481 rows

from the first table."

So it's an exact match from product to product.

The other thing to pay attention to here

but not something that we necessarily have to worry about

is the join type, right?

So by default, this join is a left outer.

And what this is basically saying

is take all of the product keys

that exist on my first table,

this Sales Data 2022 table,

and grab any rows that you find a match for

in my second table, this Product Lookup table.

And for our purposes, this is gonna do the trick.

So everything looks good here, we'll click OK.

And so you see what happens here,

we've added a new column

but the column looks kind of funny.

It says, "Table," and it's formatted with these links.

All right, so from here,

we actually have one extra step that we need to take,

and it's to click this Double Arrow button here

to expand the table and all of the contents within it.

Right, so by default, all of the columns

from the table that we wanna join are selected,

and if we were to press OK here,

this would add all of these columns as part of the merge.

And honestly, that's not really what we want.

So we wanna go through

and select the columns that we actually wanna see.

So let's start off, we'll select Product Key.

This will give us a way to kinda compare results.

And then let's say we want Product Name and Product Color.

Our last checkbox here,

Use Original Column Names as the Prefix,

if I leave this selected,

it's gonna create a prefix for every column name

with the name of the product table, right?

And we'll see what that looks like in a minute.

I'll just leave this selected for the sake of this example.

So we click OK.

And now we've expanded the Product Lookup table.

And now we've got this new information

that didn't exist in the sales table

up until this point, right?

We've got Product Name and Product Color detail,

and it's populated for every single row of the table,

just like it would if we were using an Excel Lookup, right?

So we can scroll over to the left here, right?

And you can see we've got Product Key 529, 529, 214.

Scroll back over.

Right, we've got 529, 529, 214, right?

So we can see that we've got all of this new data added in

for every record of the table.

And, like I hinted at earlier,

just because we can do things like this

and merge tables together

doesn't mean that 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 by using table relationships.

Again, we're getting there soon.

We'll cover relationships in the next section.

But for now, let's go ahead and we'll delete that merge.

And now we are back to where we started.

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