Exact & Approximate Match Lookup Formulas in Power Query. 4 Amazing Formulas. Excel Magic Trick 1747

in power query we want to see how to do exact match lookup and approximate match lookup this is the sales table and based on the product name i need to go over like i had vlookup or xlookup look up the product name bring back the price then i need to use the units column doing approximate match lookup to get the correct discount and bring it back to the sales table now i've already done a video on both exact match and approximate match lookup and just a couple days ago minda and phil did a video also and so we want to look at two formulas for exact match lookup and for approximate match lookup and the cool thing is we'll see how the formula for exact match looks up the column then the record and then we'll do it looking up the record and then the column and then for approximate match lookup we'll see how to use a custom function variable and a let expression variable and these two formulas will first select the rows and then the column and this one will select the column and then the row so we'll see how to create these and then i want to hear in the comments below which one do you prefer and why now doing lookup and power query is not as easy as it is in the excel worksheet for example power query has no x lookup or vlookup equivalent it can do two-way lookup sort of like the index function because the index function needs a row number and a column number in power query the basic two-way lookup formula is please give me the table name and then in curly brackets which serve as the positional index operator please give me the row number then in square brackets the field access operator please give me the column name now there's some variations on this as we will see but that's the basic way to do two-way lookup in power query now here we are in the power query editor and in this sales table we need to look up based on the product in each row the price and in this first formula we're going to look up the row first and then we'll get the column that will give us the intersecting value so in f sales we go to add column custom column we'll name this price and we'll start off by just getting the table d product if i click ok that means i have the full table in each row now because we have a unique list of product names if we come back to the dialog box i'm going to click the gear icon we can use the positional index operator curly bracket and inside we need the correct row position for the price we're trying to look up and power query is base zero so if i put a zero and click ok i'm looking up the first record but i want it to be dynamic now inside the positional index operator those curly brackets when we use square brackets it's not a field access operator to look up a column from a table it's a lookup operator and it allows us to look up a field from that table without using the square brackets so we'll type product and we run a boolean test hey are any of you equal to and remember that's a unique list and now we use the field access operators and in fact we can just come over here and double click this and that allows us to access the product in each row this is called a key match lookup because that product column contains a unique list a primary key that allows us to check each one of these products against this unique list so now inside the curly brackets we have a dynamic way to look up the correct row position to return the record from that d product table so when i click ok now i get a dynamic record quad when there's a quad and carlota when there's a carlotta now we need to get out the price column to get the price so we'll open up the dialog box and after the equal sign we'll use the record dot field function open parentheses it needs a record and then notice it's field as text so we type a comma in in double quotes we type the name of the column that's what we're trying to look up close parentheses and that's our formula that first looks up the row and then the column when i click ok we get the correct price for each row now let's add a data type up here table dot add column there's a fourth argument so at the end we type comma type for data type and then number for decimal number data type i'm going to click very carefully at the end and enter and now we have our exact match price lookup now we'll do exact match a second time so we'll add a new custom column we'll call it price2 and then this one will look up the column first so we'll do d product and then our field access operator and we're trying to look up price so tab that will return a list of all the prices the same exact prices in every row now based on whatever the product name is i need to pull out the correct row so we'll edit we'll use our positional index operator and inside we're going to use a function called list dot position of and when i open parentheses it needs a list and we need to match the product here row by row with the full product column from d product so we type d product tab field access operator product and that's our first argument comma and now we can just double click product and then close off list.position of and that formula looking up the column then getting the row position number we'll do our two-way lookup when i click ok there's the price up in the formula bar in the last argument of table dot add column type dot number you know in here if i hit enter it puts that in intellisense doesn't always work in power query so i click at the end and hit enter so we did exact match lookup looking up the record and then the column here we looked up the column and then the record now when we did exact match lookup we used table.add column and it used the keyword each which is shorthand for a custom function which iterates down each row in this table when we do approximate match we're going to use table dot add column and each but then internally the function we're going to use after the each keyword is going to have to iterate down this column so we're going to have a second occurrence of the each keyword and in order to do that we have to define a variable so we're going to see two different ways to do this we'll see how to do it with a custom function and we'll see how to do it with the let expression now the essence of each one of the approximate match formulas to look up the discount is going to be the same for each row we have to take that value 160 go over compare it by asking how many of you are less than or equal to that 160 that would give us all rows and then of course whatever the last one is the one we want if we're looking up 10 the answer to the question how many of you are less than or equal to 10 is just the first row so then the discount would be zero all right let's try over in fsales we'll try the custom function first we're going to add a custom column we'll call this discount and we start with the whole table the discount table and if i click ok that just gives me the same exact table now i need to filter the whole table based on this field here so we'll edit after the equal sign we'll use table dot select rows open parenthesis table is the first argument comma the second argument requires a function that's the formula that will iterate down d discount and because we're going to have to access fields inside the d discount table and the units column we're going to have to define a variable in a custom function open parentheses and the variable we're going to define for the fields in this table is it the each keyword will still work for table dot add columns to get to the units field out here but now we have what we want the variable to access the fields in d discount followed by the go to operator equal sign greater than and whatever comes after this is the formula that's allowed to use that variable now i want to access units inside this table here so we preface it with it and then we ask the question hey column are you less than or equal to the available columns here and double click units now this is the comparative operator less than or equal to that's just the syntax that microsoft chose to say everything after this variable is our custom function or our formula when i close parentheses whoops there's an extra square bracket but that formula will give us a filtered table when i click ok the top table should have all the rows 57 should have three and 10 should have one now we need to get the discount column so we edit table dot select rows is delivering a table and how do we look up a column and return it as a list field access operator and then discount close square bracket now when i click ok we have a list oh there it is 4 4 57 there should be 3 and for 10 there should be 1. now we always want the last item so when we edit after the equal sign hey that's a list so we say list dot last and then close parentheses and when we click ok there we have looked up the discount table dot add column at the end comma type number so with this approximate match lookup formula we looked up the row or the record first and then we looked up the column and took the last item in that column in addition in this formula we dealt with the fact that we had to work with two different columns from two different tables by defining a variable in a custom function now let's see how to do that using the let expression now this will be our fourth custom column that we create we'll call it discount2 and we're still going to need to get the units from each row and ask the question of the units column from d discount which ones are less than or equal to whatever the unit value for each row is now we're going to bring in units into our formula using the let expression so all lowercase let and let allows us to define variables and then say what output we want so i'm going to name the variable u for units and we say equals and since this formula is going to iterate down this units i can simply double click there's the field axis operator to get the value in each row now normally in let we have lots of steps but we're only going to define one variable then we use in everything after in is what we want the let expression to deliver so i need to filter the d discount units d discount that's the whole table and i'm going to use field access operator to get units once we put a field access operator on a table it's delivered as a list so to filter or select a certain number of rows we use list dot select open parenthesis the first argument needs that list and there it is function now because we defined u this input from each row here as a variable we do not need to define a function variable here we can just use the keyword each which is a stand in for a custom function and since we want to access each row in this list when you use each all you have to do is use underscore now this is a list so that says please get each item in the list if each and underscore are working on a table then it gets the full record now for each item in that list i say hey which ones are less than or equal to the variable u which is each units from this column close parentheses on list dot select and i forgot a comma to separate the first argument which is our list and then the custom function so that will work when i click ok now we get a filtered list now what we're doing here there's the zero for 10 and the three rows for the 57 is now that we have this three rows we want to count how many rows there are and then subtract 1 to tell us the position of the last one now we want to edit this and let is what is delivering the list so before let hey we need to count how many rows are in that list so it's list dot count but this will count all the rows starting at one and power query is base zero so we subtract one and now when i click ok all i get is the position of the correct discount in that table over there so we edit and after the equal sign there's the discount table but we want to look up using field access operator the correct discount and then from that list we use our positional index operator and this cool formula i learned from phil and minda in their video posted a few days ago when we click ok bam there it is we'll add to the last argument of table.add column type number [Music] and enter so in this approximate match lookup formula we looked up the column first and then we got the row and because we used two different columns from two different tables in our formula we use the let expression to define a variable and then in this approximate match formula we looked up the rows first and then the column and because we used two different fields from two different tables we had to use a variable and we used a custom function now i want to hear from you in the comments for exact match lookup do you like to look up the rows then the column using record dot field or do you like to look up the column then the row using dot position up for approximate match do you like looking up the rows and then the column and using a custom function or for this one do you like to look up the column then the row using a let expression or maybe you have some formula you like even better alright if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos to come from excel is fun all alright we'll see you next video

Channel: ExcelIsFun

Views: 10,960

Rating: 4.968421 out of 5

Keywords: Excel, Highline College, Mike Girvin, excelisfun, excel is fun, excel fun, excel magic, Excel Formulas, Excel Functions, formulas and functions, Excel Magic Trick 1747, EMT, EMT 1747, Microsoft 365 Excel, Office 365, Excel 365, Exact Match Lookup, Approximate Match Lookup, Power Query Formulas, M Code Formulas, Power Query VLOOKUP, Power Query XLOOKUP, Lookup Formulas in Power Query, List Functions, List.PositionOf, Record.Field, let expression, Custom Function., Vlookup

Id: XPDFpK5yCrI

Length: 16min 30sec (990 seconds)

Published: Tue Aug 03 2021