1-4A
0 (0 Likes / 0 Dislikes)
One of the really common operations
when you're bringing data into Power BI
is to merge data together. It's like doing a join operation
if you're writing a SQL statement or something similar.
And we have both ways that you can append data
so that they can be sort of stacked up one after another
and also merge things, which is like the join operation.
Let me show you how that works.
So I've got a few different data sets that I've already brought in
as a few different queries here.
I've got this product table with our manufacturing column,
and then I've got a little table which just sort of maps
the manufacturer to different sales contacts that we have.
And now I might want to join these two tables together
kind of doing a left or right join like you do in a SQL query.
And we have this option up in the ribbon to merge queries.
I can either merge an existing query onto this one,
and it would be appended as extra columns.
Or I could merge two queries together as a new query,
and you end up then with a new entry in this list on the left-hand side.
I'm just going to merge them together within this product query.
I'm going to get a little dialogue to allow me to choose
which tables I want to join together
and then how I want to join them as well.
So like I said, it's similar to doing SQL joins.
You can choose left or right outer,
and the joins are only rows that match
one side or the other of the join.
I'll do a traditional left outer join.
And then we have to select the fields that I want to join on as well.
I've got this manufacturer column.
I'm going to select that in both tables.
And I'll get a little preview just to tell me
how many rows from those two tables are going to get joined,
how many have got matching options.
So I hit okay, and now you can see that it's been brought in
and added to this product query as this new column.
This is represented as a table here.
When I click on these, you'll see the individual row
that was mapped between this one and the other table.
And I can choose this little expand icon
to say, "Okay, which of those columns from the other table
do I actually want to include in the results of this query?"
So actually the only thing I'm really interested in
is the salesperson and the base location.
Manufacturer is the foreign key column, if you will.
So all I need to do is expand those,
and now we've got those two extra columns in this query.
So that was a really simple way of just bringing data in
and merging it into a single query within the query editor.