 Hello everyone and welcome back to another video. In today's video, we're going to be taking another look at power query In this particular example, we're going to be looking at how we can merge two tables So at the moment you can see well, we'll go through them We've got two sheets users and contacts in the users table We've simply got a user ID a name and a surname and in the contact table We've got that same user ID and it but we've also got now some additional contact information for each one of those users and what we want about to do is We'll start off with email But we want about to pull through either one or multiple columns from this contact table And map them against each of our applicable users in this table But we'll be creating creating all this into a new table altogether So you could say it somewhat resembles The functionality that vlookup gives us But we want to obviously moving away from those vlookup functions We can now see how we can do this in power query And it's obviously a real strong function because like I mentioned just then We we have the ability to pull through multiple fields We could pull through every one of those fields in the contact table rather than just the one Again as that example of why power query is better than or more powerful than our vlookup in that example So we'll just jump straight in if you haven't seen our previous video on power query You might want to check that out Maybe after this video But it's also worth mentioning that if you have little or no experience using power query This video is still suitable for you And you'll get a great idea of just how straightforward power query is to get the basics And then obviously you can then build upon them as well And it's a real great tool for saving you time when it comes to particularly repetitive or monotonous tasks And yeah, that is that's my sales pitch on our power query. So let's jump straight in So the first thing I'm going to do is go click obviously where I am here and you can see in the user table This isn't a complete applicable step It's just the way I tend to do it And I'm going to navigate in our ribbon to the data tab and then you can see we've got this optional from table range So this is where we're going to be opening up power query and telling it we want to absorb data from our users table So I'll click that button And you can see it's now loading up and we've now got one query Query that is being created for us. So we can see we've got this user user's query on the left hand side here And we can also see that power query has built two Applicable steps for this particular query to the first one being source So we can see where this query or the data is coming from and clearly we can see it's an excel connection It's coming from our current workbook and it's picking up an item named users So we obviously know visually that we can see that information is stored in an actual table But when it comes into power query, don't get confused It's just referring to the name of that item, which is a table But it's the name is obviously users and you can see it was absorbing all of the content And it's also added this change type step. What simply is Identified what it believes to be the best format for each of our fields So depending on the data set you're using you might want to play around with these All you can all you need to do is either manually type It will replace in the text for each one of these items here Or simply you can just select in the top left of the applicable field your desired format for that field as well So at this stage we have successfully pulled in our users table What I want to do now is I also want to put in a separate query for our contact table And then after that we can then have all the information that we need to pull upon it There are as with many things I'll probably touch on this video in multiple ways you can go about this But the straightforward way I find and also it'll help you understand how things are working Is we can simply recycle what we've done here for our user table to get our contact table All I'm going to do is navigate to our source And I'm just going to copy out all of this information that you see here or this simple query we've got And I'm never going to navigate to our query section here. I'm going to simply right click go to new query Go to other sources. Obviously, you can see we've got all these various sources that we can pull from as well Go to other sources and I'm then going to navigate down to blank query And you'll see it's now created a query for us Simply going to do control v to paste that in And if we were to hit enter now, we would get basically a replica of our users query But we want to just change this to contact Or again, if you had another table you wanted to pull you'd work the same way if you just call reference that query hit enter And you can see it's now pulled through that query or that data set for us as well And slightly different all looks the same. So source again the same what we just now entered and change type You can see automatically power query has then gone and identified what it believes to be the best format for each of those fields Again, we're not going to pay too much attention to this at this stage But as mentioned before you could play around with the different formatting if it was a key requirement for you We're just going to rename rename our query now and that can be done either way You can do it over here in our name field Or you can just simply right quick click the query Select rename and I'm going to call that contact and hit enter So we now have our two queries that we want to merge together So our next step is going to be having got um What I find I do by default is I select the sort of the driving table. Should I say so this is the base table? So In our particular example, we're looking at here We have exactly the same number of rows in both of our tables because it's simply a table that was split out But there could be a scenario where your user's table Maybe only contain 10 users your contact information table then contained many or maybe hundreds of rows And in a scenario where you only want to pull through the applicable contact information for the people you have in your user table That is where you want this to be your primary because it's kind of driving everything. So that's why I've selected here first Having selected that I'm simply going to go over to merge queries And again, if you're on a different tab, just make sure on the home tab And then over about halfway across the page, you'll see you've got merged queries But rather than hit that button We're going to do the drop down here and you can see that we have two options available to us We can either do merge queries in which the merge will happen within this existing user's query Or we can go to the second option which is merge queries as new And what I want to do is go for this option because it will then create a third query for us in which the merge has happened Again, you can I'll just select that button now Again, you can it depends how you want to work if you want to merge it all in one But I quite like to break out the steps. So I've still got my separate user in contact queries And I can then just you know, I have my third one here. So you can see how it builds out So the first thing it's going to have done is pulled our users table through and we can see a preview of that here And then the next part is we wants us to select our second table that we're going to be merging And for us it's quite simple. We haven't got many queries to scroll through But if you had more, obviously you'd see a longer list here and all I'm going to do is select this contact Option or query should I say and we can now see again a preview of all that So it just gives us the first what five or four rows that we've got there Once we've got that this is where it's really important Obviously have your unique identifier across both queries for us. It's the user id So we just now need to select user id from each query What I've done in the first and also done the second And you can see it's been selected because it's been highlighted in each query And that's allowing the two queries to be connected And obviously for us to be able to pull through the data as required The join type we then need to select and you can see there's a number of options here I don't want to digress too much into this and create confusion But you can see a brief description of which what each of the joins will do And so for left outer what we've gone for you can see it says all from first Matching from second. So basically our final query will show all of our results from our first table But will only Pull through the matching results. So if this person doesn't exist in this table So let's say Alfonso if Alfonso doesn't appear in this table our final query will still have a row for Alfonso It's just that we won't have any data pulled through for him If however, we did a right outer join so we can see it's pulling everything from the second But only the matching from the first Then what it's going to do is is our query is going to basically show us all of our final table Our final query is going to be all of our second table And it's only going to then add the where applicable the people from the first And I think I might have been trying to explain that simply and maybe made that more confusing than it need to be But hopefully I haven't And more importantly for this example, if you just select left outer, it will get you started on how it works So once that's done, we can ignore this funny fuzzy search options Just just gives us the ability to find or to define how accurate we want to be with our mapping But we'll skip over that for now. We can see we've got a tick here. So it's all happy things are working If we then select okay We can see that we are now left or we have new a new query created So we can see merge number one and it's going to very much resemble our users table at this stage So if you go to users, you can see we've got these three columns And if I go to the merge, you can see that we've also got those same three columns But we've also got this contact field now available, which is a table So to now show the information we require from the contact table We're going to go on to this expand button that you can see at the top right here Not obviously to be confused with a drop-down filter that you'll see on the first three You can see the icon is ever so different with the two arrows. I'm just going to select that And if we wanted to now bring through everything that appeared in the contact table We could do that by having them more ticked ticked But I want to start off by just bringing through the email field So I'm going to do is deselect all of them select email And then I'm going to select okay And you can see that we've now got our email address available to us So as we look past the eye down this page, you can see we've got Abbott Hansen and we've got Abbott Hansen.icloud.com So we can see that they are all aligning and they look as they're expected to So we have now got our query as desired If however we suddenly decide actually we want more than just the email address What we can do is go back into our Settings Oh, and I've gone to the wrong one. Oh, actually now I think we can do it from here You can see at the moment we've got email just selected if we also wanted to bring through the phone I'm just now going to select that as well and click okay If I can hit the okay button and you can see it's also added the phone number for us as well And if you did miss what it's now clicked on over in our applied steps You'll see and it might not show on the screen too well It says expanded contact and we can see we've got this little cog in the corner So if I select that cog You can see it opens up our options of Information to expand So all you need to do is just make sure the applicable values that you want are selected. Let me just do another one Let's go on to region select okay And you can see how it's now brought region through for us as well If however you change your mind and you don't want region or another one go back into that cog D select region Select okay, and you can see how the formula was updated for you both at the top here and obviously in the data we have available here as well One last thing you might want to do before closing this is you can see that at the moment The information is called titled contact phone or contact email because it shows you where the table has come from I'm simply going to just Rename both of those so I'll click right click the contact first and just change that to phone And I'll also do the same for contact email right click and let's just call that email Just so it tidies up ever so and you can see we've also got that step now created for us as well And if you want to where you can just navigate back through these steps so you can see the process as it unfolds I'm happy with everything I've got here now. So I'm just going to simply close and load in the top left hand corner And you'll see it's now created a number of additional sheets for us what we'll touch on in a minute And we've also got our desired output so called merge one What we because we did end up with that because we didn't rename it. So let's just go back into merge one And sorry I did that a bit quick didn't I what we've got there is obviously this is our output And you can see we've got this option on the side here for queries and connections If that box is not available to you So if I just cross off of that simply just go to your data tab and you'll see there's this option here for queries and connections Once we've got this open we can I'm just going to double click on merge one to go into the power query edit for merge one And let's just rename this. So we'll call this uh, I don't know Contact Details maybe Yep And we need to make sure we do close and apply or close and load If we did just cross off of that it wouldn't save our changes And you can see it's changed the data set there, but it hasn't obviously changed our sheet name So what I want to do here is let's just Delete that and also demonstrate something else at the same time. So you can see we don't want that We don't need contact. So what it's done is it's created a sheet for every query that we have And we don't need to save that and we don't need to do users as well Okay, we just need to keep obviously our source user and contact table So if we now to go and do a new sheet and I was going to select the top left there So all we then need to do is if you go to contact details in our queries of the side here, we'll hit right click We'll then go down to where it's a load to and I want to store it into a table And I want to then uh, yeah, so you can see it's already then selected our existing worksheet You could go into a new workbook, but I'm quite happy for it to go there And I'm going to select okay, and you can see it's now pulled that field through the field So it's called pulled through that data set for us as desired And obviously we can then rename this now for to output maybe or whatever desired name you have And there we go We've got our desired output the real benefit obviously of this is should any of the information and our source table change It will reflect in here. So let's say abat Hansen at iCloud.com. Let's see if we can find Abbot Hansen. So Abbot Hansen is e 10 83. So let's go to 10 83 And let's change this to I don't know Rather than our club ago new email just so hopefully stands out on the page to us And we'll just remove that Filter I've got on the table if I now go to output we can see what is old email here Simply you're going to right click refresh And you can see it's now updated to abat Hansen at new email.com So I hope you enjoyed that video and you found it useful Maybe this was something you were searching for and needed a solution Or this is now a new feature that you previously weren't aware of apologies as I think the video has gone on a bit longer than I intended But I hope it was only more valuable and obviously a bit more detail to help you through this process If you did enjoy the video, please do give the video a like It's not only greatly appreciated by me But obviously helps that all important youtube algorithm and ensure More people are able to find such videos as this one If this is your first time checking out the channel or you've seen our videos before and you haven't yet subscribed Please could you also do me one last favor and subscribe to the channel and hit that bell notification button That way you will also be notified as soon as new videos of ours come out onto the channel So once again, thank you very much for watching and we'll see you in the next video