XLOOKUP v Power Query v Power Pivot in Excel

if you need to combine the data from one table onto the side of another table you can use x lookup power query or even powerpivot here's a quick summary of the pros and cons and little trap to beware of with power query let's go here's the scenario i've got a little table of data with item a there's three of those item b three of those and item c three of those but i wanna do some reporting that has um the actual names and the cost so i need to get this data over onto this table let's start with the simplest one which is x lookup if you're still using vlookup or index match then really check our x lookup a full-on video that i've done about x lookup will appear there's a little link up here this is just a quick taster so equals x lookup you just pick the value put a comma go and highlight the range you want in this case this column here comma and bring back what do you want to return and you can actually just click on the heading that's where i want to return and it's looking to the left okay which is awesome you can press enter it's done okay so this is the item beautiful you can even okay when you're doing your x lookup rather than just bringing back one column you can bring back adjacent columns i can bring back both of those and press enter but you can't do it inside a table you get the spill error okay still a problem right so you do have to do this twice if you want the results to be in a table but nice and easy let me just do it again equals x lookup it's so quick click on this item comma highlight where you gonna find that result comma highlight the column wherever it is that you want to bring back and this time when i bring back cost so i press enter if you want to handle missing items okay you can put comma and then missing so no more if not found okay no more if error sorry no more if error wrapping around your formula beautiful and that's cost okay and if something wasn't found so let's say i had this um item f you get the word missing because i put that little wrapper on the end missing whereas this one i didn't and it shows any okay great so that's x lookup okay i'm just going to delete those power query right let's show you this and i'll just put this back to a so we're starting the same spot so right click get data from table slash range now i've got my table in my data in tables really important to set your data up like that ctrl t to turn your data into these tables and even give them names by the table design menu up in the top left corner okay give your tables proper names just makes it so much easier to interpret debug work out inherit from somebody else right right click get data from table slash range or on the data menu there will be the from table slash range option what i'm going to do is say close and load this as connection only so close and load closing load two and make sure i pick connection only only create connection click ok i'll just repeat that for this one right click get data from table slash range okay and you can change your default settings for this i'd recommend people change it to default to connection only it's just a bit more flexible only create connection beautiful i'm just going to double click on one of these right so all i'm going to do is a merge okay a power query merge so i'm going to click on my item go to merge queries right click on my drop down with table look up and it's this item with this item and if you had multiple items that you needed to sort of you didn't have a single unique key you can click multiple columns which is pretty cool okay so there we go and we're clicking ok and then we just expand out this little column here and say hey i want the cost and the item untick that click okay and there we go we've got a nice merge and a lookup okay beautiful actually i didn't want an item i wanted units so i go back in here i say there i want the name and i click ok too easy okay so if you want to make a change click on a little cog so all good and i can click close and load and then i just want to load this to a sheet so right click load to a table an existing worksheet you click ok and now we've got the result okay so why would you do it that way well you do it that way if there's more power query stuff to be done um and also if there's you're pulling the data from external data sources rather than just doing an x lookup inside the file so you can pull day from here pull the lookup table from another centralized spot those sorts of things okay and also you use power query if you're going to load it into powerpivot which we'll look at in a second but the warning you've got to be careful about duplicates so let's say accidentally somebody else put another apple a in here okay the x lookup would just bring back the first a and bring you that back these two items unless you do a reverse search in x lookup and then it brings you the bottom one but it's the same thing but check out what happens in power query i've got nine items here okay right click refresh whoa what just happened look how many a's i've got i've got six because there's two ways here the merge has doubled them up okay and the numbers are all doubled up over here that ain't good okay that's not good if i change this a to let's say 55 just to make it really stand out and right click and refresh you see it's bringing back both a's so you've got to make sure that your lookup table is unique now one way of doing that okay if you're comfortable with this is just simply remove duplicates on your lookup table column but all that is going to do is keep the first lookup value so maybe this one's right the 55 maybe that's right but if you simply do a remove and you go remove duplicates it'll just keep the first one the first a so an upcoming power query challenge stay tuned uh how to address that and give a warning to the end user okay right so that's just a bit of a warning about about those things okay the last one uh let me close that down the last one i want to show is using the data model so rather than loading this to a table so i'm going to go load to only create connection add to the data model so i've built it in power query i've pulled the data in okay and go okay i'm just gonna do my lookup table as well so i'm just gonna go load two and load that into the data model and i just need to tweak my query i don't need to do the merge when i load to the data model so check this out i'm just going to go back into power query i'm going to get rid of the merge step okay so i just want that that's it close and load so these have gone into the data model so where is this data model well it's under the data tab so i'm just going to go here data okay manage the data model click on that and you need to relate these two tables to each other in the diagram view so diagram view i'm just going to create a link so here's my data here's my lookup and the common thing is the item okay so this is saying it can't do it because there's duplicates so let me which is good okay it's it's avoiding this is the problem okay so it won't let you create the relationship so let me delete this row let me refresh so right click refresh to load that back into the data model let me go in here and recreate the link beautiful okay so what good is that to us well this could be the equivalent of 50 million vlookups or x lookups right this is the power and this is awesome because then i can just simply go into a sheet and say insert pivot table from the data model click ok and then i can actually pull in from a reporting point of view the name okay from the into the rows i could bring in the uh date and the units or whatever i need from my reporting i could bring in the cost etc okay so all those things are in there i've got the units i haven't sold any dates so let me sell some dates as well to d for dates and i'll put that to a hundred and then you just have to refresh everything okay so you just go data refresh all and this will all update so for reporting if you purely got pivot table reporting go straight power query into the data model create a relationship if you're pulling sources from external information you want to load it into a table um then potentially merging but be careful about the duplicates and good old x lookup you know that should be your first choice if you can if it's nice and simple go in x lookup hope you find that useful let other people know about the channel catch you later [Music] you

Channel: Access Analytic

Views: 18,836

Keywords:

Id: KdF-OQ8G5FY

Length: 10min 48sec (648 seconds)

Published: Sun Sep 11 2022