Now let's take a look at how we can merge data in power query.
So basically merge data from different tables into one report.
The great thing with using power query is that you can merge data from different places.
They don't all have to be Excel tables in the same workbook.
You can have a text file like we had in the previous lecture.
You can have one table sitting in the workbook and other data source sitting somewhere else.
As long as you can create a connection to it with power query you can merge these data sources together.
What we're going to do in this lecture is to build on what we did in the previous one. In the previous
lecture we imported a text file that had this information.
This is a query that's connected to our text file. And we can see that by going to data, queries and
connections,
that's our query.
If we double click it, we open up power query and we can take a look at the steps that we applied here.
Now what we're going to do is to merge this information together with this master data information.
So let's say for the article code I actually want to use the description in my final pivot table.
I don't have it in the source data.
I just have the article code. I want to bring in the description in here as well.
Now one way of doing this is to use the VLOOKUP function. After I imported the data here
I can add a new column, use VLOOKUP and look up the data from the master data.
Another way of doing this especially if you have a lot of data is to use power query. And the great thing
is you can already add it as a part of your query steps.
So every time you import this data you can already merge it and bring in the description.
Let's go and set this up.
First off, to be able to merge different queries together
you actually have to create a query for each of your tables. So, for each of your data sets.
In this case I have the information sitting in an Excel worksheet.
What I need to do before I bring this to power query is to transform this into an Excel table.
I'm just gonna press Control+T. Or you can go to insert, and insert a table, table has headers.
Let's go with Ok. Let's give this a name.
I call it ArticleMaster. Press enter and let's take away the table style to go back to our original style.
Now that this is a table, I'm going to create a query on this. So that every time this updates as well
my query is going to update. Go to the data tab and click on From table. I can see the information in
power query, Article Master is fine.
I'm not transforming it and getting a different result.
Otherwise I would change this name.
But in this case I don't need to. I just double check the data types applied to these.
That's fine,
and text is fine. So that's it.
I basically don't have to do anything except I need to load it somewhere. But obviously I already have
it in my worksheet.
I don't want to load it there.
So what I want to do is to go to close and load it to and only create a connection to this.
Basically just keep this in memory and then click on ok.
This means I have a query, every time this updates this information is going to get updated.
My next step is to merge this query with this one.
So I'm going to right mouset click on the first query and select merge. The dialogue box here shows me my
first query which is this one and I want to merge it into Article Master.
I have to select what is the connection between the two. The connection is the article code.
Just select it and select the article code from the second table as well
and then you can select the Join Kind.
Now the default is left outer. Which means all from the first, matching from the second. Which is correct.
This is basically the standard VLOOKUP.
You have a lot more options here as well.
So once you have time go and explore these options. Down here you can see that it found a match for
136 of 136. So there was no information that's missing
and then click on
OK.
We get power query opened up here. We get a new query called merge one. Let's changed the name. I'll call
it CompleteSalesImport. For each of these lines we get this article master. And attached to it is a
table. And if you happen to click on one of these
notice that we see more information of this table. Basically, for each line of data it has attached
this information.
Now we don't want the whole information.
All we want is the article description.
I'm going to click on this arrow up here and from the filter just put a checkmark for description.
And it wants to use the original column name as a prefix. But that's not something I want so I'm going
to uncheck it and then click on ok.
So I just see description.
I can also change the title by double clicking. So I'm just going to add in article description and you can
see that step recorded here.
Now as a next step
I'm just going to move this and put it right beside the article code.
Notice we see reordered columns.
Now I'm done with my query so I'm gonna go and close and load it to.
Now here I can directly create a pivot table report.
I could bring these in it's own table as well but I'm going to go with a pivot table report.
Let's put it on a new worksheet and then click on
OK.
Now I can create a report that has information on articles so just the article description, the quantity
sold, and the sales in USD.
Amazing, right? As a last step
Let's go and check how our query is going to update once our source data changes.
Let's go back to our original text file here and on the bottom.
I'm going to add two lines and use two article codes that don't exist in our list.
I'm going to put 210 and 220.
Close this and save.
Okay so now what happens when I refresh this query?
Click on refresh.
I see that I have a blank row for the article description.
It couldn't find the article code. To double check where this number comes from.
I'm going to double click that's going to create a new sheet and show me the line details.
This is the information I just updated.
I added these two Article codes. So let's just remove this sheet, so it was 220 and 210.
Let's go to our master data and add this information. So 210 is a silver watch and to 220 is a gold watch.
Now let's go back to our pivot table and let's just refresh this. And we get our information right there.
This means that any time we update our source information or our master data information, our merge
query will apply the original steps and then merge these together.