Two-Way Lookup in Power Query? M Code for Exact & Approximate Match Lookup. Excel Magic Trick 1554
Вставка
- Опубліковано 17 лип 2024
- Download Excel Start File: excelisfun.net/files/EMT1554S...
Download Excel Finished File: excelisfun.net/files/EMT1554F...
Entire page with all Excel Files for All Videos: excelisfun.net/files/
In this video learn how to create Power Query M Code formulas for Exact Match and Approximate Match Two Way Lookup.
Topics:
1. (00:06) Introduction
2. (00:40) Exact and Approximate Match Lookup
3. (01:44) Worksheet Exact Match Two-Way Lookup with VLOOKUP & MATCH functions.
4. (04:11) Power Query Exact Match Two-Way Lookup using M Code formulas. Learn M Code Syntax for looking up a Record. Use the Record.Field Function.
5. (10:40) Worksheet Approximate Match Two-Way Lookup using an Array Formula with VLOOKUP & MATCH functions.
6. (13:25) Power Query Approximate Match Two-Way Lookup using M Code formulas. Learn about Custom Functions. Use the Table.AddColumn, Table.SelectRows and List.Last Functions. Learn syntax for Custom Functions, including the each keyword for Custom Functions or explicitly defined M Code Custom Functions. Learn how to create formulas with back to back custom functions.
7. (20:55) Summary
Topics:
1. (00:06) Introduction
2. (00:40) Exact and Approximate Match Lookup
3. (01:44) Worksheet Exact Match Two-Way Lookup with VLOOKUP & MATCH functions.
4. (04:11) Power Query Exact Match Two-Way Lookup using M Code formulas. Learn M Code Syntax for looking up a Record. Use the Record.Field Function.
5. (10:40) Worksheet Approximate Match Two-Way Lookup using an Array Formula with VLOOKUP & MATCH functions.
6. (13:25) Power Query Approximate Match Two-Way Lookup using M Code formulas. Learn about Custom Functions. Use the Table.AddColumn, Table.SelectRows and List.Last Functions. Learn syntax for Custom Functions, including the each keyword for Custom Functions or explicitly defined M Code Custom Functions. Learn how to create formulas with back to back custom functions.
7. (20:55) Summary
21:07 do u haveor can you make anothertraining on those table names(IT) and(OT) i know it must be easy but now it sounds comlcated.
Thank you for the 2-way lookup with M code :)
You are welcome, Teammate!!!
Which way the fastest to lookup ?
Where can we hit thousands and thousands the Like button? WOW. I simply love your videos! All of them! Just amazing. Thank you so much.
Thank you very much for the 1000s and 1000s of likes, Jimmy!!!! Thank you for your support on each video : ) : )
It is amazing that this knowledge is being shared at no direct cost to the viewer! Thank you!
For 13 years at UA-cam, I have been providing free Excel Education for the world!!! : ) But: it is actually not free, I do charge a thumbs up for each video that you learn from ; )
"OT" and "IT" naming convention = super easy to understand therefore to explain to others!!! I have to borrow that from you :)
Thumbs up as always!
Hi, Mike!
Thanks for the video!
This way of compare like the one you are using =ProductPrice{[ProductID = [ProductID]]} is prone to errors if you have a duplicate ProductID's in Dimension table, so the behaviour is not the same as VLOOKUP, which grabs the first found value. I am reading a book wrote by Gil Raviv and found a function for the list: List.PositionOf which works the same as MATCH in excel does. And it's not giving errors if duplicates exist in dimension tables (also returning -1 for values that are not found). In your example it may be rewritten with this =ProductCityPrice{List.PositionOf(ProductCityPrice[ProductID], [ProductID])} and will work the same way. And the final formula will be =Record.Field(ProductCityPrice{List.PositionOf(ProductCityPrice[ProductID], [ProductID])},[CityStore]).
Have you used List.PositionOf before?
That is a great trick: List.PositionOf!!!!! Thanks for that trick Alexandr, passed along from Gil : )
In the full class I teach on M Code, we talk about primary keys in Power Query, and how most Key Match Lookups {[Column=Criteria]} are done on columns with primary keys so this issue does not occur. The List.PositionOf tick will come in very handy when there are dups and we need the first one.
@ExcelIsFun comes through again (and again and again)! Thank you SO much!
Today I’ve been working on some big data PQ-M (was easy) and some PP_DAX (not so easy), nailing the PQ-M and making good progress on the PP-DAX. I was feeling rather smug and happy about it. Then I saw this video... and -yeah- that put me right back with my feet squarely on the ground. Thanks Mike, for showing us that there is always more to learn! WOW! Love this! :-)
You are welcome, Geert!! I am glad that you love it ... me too : ) : )
You surely needs a thumbs up to the power of billion to this nice Video.
Thank you for showing both Exact Match and Approximate Match Lookup inside of Power Query! This is really great stuff!
This is out of this world kind of stuff.
Also I like the way how you bring a typical real work example showing how typically many of us will did and how you transposes it using Power Query,! Thanks again!
Are you saying you liked how I showed it with Worksheet formulas, and then with Power Query M Code?
Your way of explaining these complex topics is AMAZING!!! Thank you so much for this!
ps: when I have Leila watching your videos, you know you are GREAT!
All the best from Brasil!
This was just brilliant, Mike. Can't thank you enough!
Thanks Mike. Saved for the week end.
Yes, it is a lot, so some study time will be good : )
And the beauty of using M is that the lookup will be automatically updated when new data is added! Thank you!! Very cool trick!
Glad you like it, Jose! Thanks for your support : )
Great job Mike!! And great assist by Bill
Thanks, Chris!!! Go team : )
You're very good at what Mike does. It brought several options for the same end result. Brilliant Mike. Thanks a lot....Thanks to bill syzys also.
Glad you like it, Luciano! Go Team!!!!!
Thanks for sharing Mike, and thanks to Bill S.
There are a lot of useful M code tips here!
Thanks mike, it is very relaxing to watch and learn new tricks from excel us fun. Thanks to bill syzys also :-) go teammmmmm
Go Team!!!!! Thanks for your support, Mohamed : )
This functionality will be of great use to some of my custom functions. Thanks heaps👍
You are welcome heaps, Kris!!! Thank you for your support with your comment, Thumbs up and your Sub : )
Thank for uploading and sharing trick with us
You are welcome, Jawad!!!!!
Great video, as always! You inspire me to learn more and more! These M code videos are so powerful!
M Code is fun!!!!!
Thanks a lot for this video. Comments are very clear and every steps are explained perfectly!! Thanks
You aer welcome!
Thanks Mike for these amazing formulas.
You are welcome, Syed : ) Thanks for your amazing support : ) : )
Best practice on M code, I love you and your training with multiple solution for each issue. Thanks, I love you and Bill.
Love is so important. I love Bill Mr Excel Jelen too - he is my #1 Excel Guy : ) I love making the videos, and you and so many of our other Teammates love watching - Go Team!!!!!
Thank you so much for your great offer. It is invaluable! I tried to do two way lookup in a table which includes 7000 rows and it took me too long to load!
Thank you for all your work. Great to learn, as always.
You are welcome for all the work!!! Thank you for your consistent support, M. SZ.!!!!
Great thanks Mike, each and every videos u shoot brings positive change in my data analysis skills, thank u so much.
You are welcome for the positive change, Ogwal!!! Thanks for the support : )
Amazing lesson!
you are god of excel.
My excel guru 🎉🙏!
I got it say this video and MDPTDA 8 were very good in day to day use ... and all videos of course ! Thank you
You are welcome! I am so glad to hear that they can help in day-to-day work, and fun : )
This is just an amazing vid! Thank you!
Wow, really great work. The inexact match seems pretty complex, but your explanation is great. I am fairly new to power Query, but am starting to see times when it is very useful. One issue I have is that it appears that you can't easily create a table of data with power query and then use Excel worksheet functions on that table. So every Excel function needs to be converted to M-code. For very complex spreadsheets this is not possible. I will start looking at your Data Analysis and BI class. Thanks so much for all this great material.
Hi Mike.. thanks for these great solutions. PQ M code is wild stuff! Thumbs up!!
You are welcome, Wayne : )
I never ever ever ever comment on UA-cam but you just saved me from a lot of sleepless nights. I am crying tears of joy right now. thank you so much.
Yes!!!!! Thanks for breaking your rule and commenting. The support you give, Thuto, with a small gesture like a kind comment, means a lot in my effort to bring free Excel Education to the world : ) : )
It's an awesome solution to approximate lookup. Thanks for sharing !
You are welcome for the share MF Wong!!! Thanks for your consistent support on each video that you watch and learn from : )
I can't ever find a teacher like you thank you Mr. Mike for your videos
Always glad to help, Ismail : )
Wonderful video.. VLOOKUP and MATCH amazing combination. Never fails to impress. In fact MATCH by itself does a simple job; but when combined with INDEX, VLOOKUP, SUMIFS etc. it achieves things beyond imagination. Cheers :)
Yes, this is billed as a M Code video, but it has some good Worksheet formula tricks also : ) THanks for your support, Sachin!!!
Amazing explanation of match lookup for M code
Glad you like it, Vida : )
Thank you so much for another great video Mike, have a great day
🤗
You are welcome for the great video, Katerina!!! Thanks for making my day great with your consistent support : )
This tutorial has saved my time...thanks ;)
You are welcome, Rupal!!!
You have just saved my day. Tks a lot!
You are welcome a lot, Thiago!!!!
Thanks Mike, this is the best introduction to M Code for exact & approximate match lookup. Thank you so much. Totally amazing.
Your video always helps me, if this is not a characteristic of superheroes, I don't know what it is then :)))
Yes, I have tried to give free Excel education away here at UA-cam since 2008. If that is superhero like, then cool : )
Very well explained :)
Glad you like it, Dipak! Thanks for the support !!
Amazing!! Thanks so much
I just watched another video about the same method in msptda section. Both where extremely helpful Mike, thanks a lot.🙏👍👍
Glad they are helpful for you, Amin!!! For me, many of the advanced Power Query Transformations seem to always end up using Two-Way Lookup, so as an M Code Skill, it is really important. Thanks for your support, Amin : )
Approxitmate match in PQ - brilliant!
That amazing polish friend of yours - that Bill Szysz guy : )
Of course ;)
Thanks Mike
Thanks again Mike - this worked perfectly and the Picture tab was very helpful to review the {} positional index operator, and [] lookup operator, as well as the inside table married to the outside table w/ Table.SelectRows() You are awesome!!
Glad to help!
Thank you very much for posting such valuable and informative power query M functions. All these power query M customer functions are amazing and mindblowing. However, I have a doubt whether these will work on a large database especially in filtering and extracting the correct row value. It would be more useful if Mike explains when and in what circumstance you should avoid using power query M steps - like For extracting previous row value data in large database use DAX functions instead of power query M functions
Thanks Mike. Downloading and following step by step. : ) : )
Awesome Sat. Fun!!!!
I'm speachless
Great vidéo. Thank you
You are welcome, sadyaz64!!!1 Thanks for your great support : )
its awesome!!
Glad it is awesome for you, Anna : ) Thank you for your support with your comment, thumbs up and your Sub : )
Thank you!
You are welcome!!!!
Very cool. Level up +++
@ExcellsFun any performance consideration if we are handling a large lookup table?
Great! Is this more efficient on the refresh time than using the merge function?
Thanks for the video.
I've been looking for a way to do regular one table with columns like Excel Index & Match with Power Query
(without having to do a series of New Query merges)
Please help.
good
Thank you for this video, I am big fan of your work! I am facing a situation where I would like to insert a part of a table into an existing table where instead of the "=" operator I would like to use ">" larger/smaller function but that seems to result error.
Table.AddColumn(#"Changed Type1", "NewColumn", each EoMonth{[EoMonthNumber>[INVDateNumber]]}) I would like to use the ">" operator. that is bring in all the records where the items of table2 are larger than the item in table1. Would you be able to recommend a solution for that please?
Awesome. Just one Q, Mike. It is up to me to declare whatever I want as my inside and outside table, or must I stick to just IT and OT?
I think by explicitly converting the first each to (OT) => was to identify fields with same name but different table like the "inside" IT. You can use any different letter, like (x) and/or (y)
Thanks Mike for the tutorial. is there a faster way to lookup the information? this video is from 3 years ago so thought worth asking
pioneer
Always glad to try and help!!!
Hey Mike! I have found an alternate Solution that does not require merging the two queries. All by learning from you.
Here is the Mcode
"= Table.AddColumn(#"Changed Type", "ApproximateMatchPriceLookup", each List.Last(Table.SelectRows(ProductsUnitsPrice, (IT) => [ProductID] = IT[ProductID] and IT[Units]
Very clever, Abdul! However, when you get big tables, merge tends to work much faster than a custom columns with Table.SelectRows.
Yes this is what I also noticed that merge is faster.
@@abdulhaseeb8027 But it is good to have too the tools in out tool kit : )
Hi Mike! Thanks for sharing this video. I've applied this formula on Power Query, but a table extraction process takes longer. I guess this formula is not suitable for my work. What I want is as follows. Now I have a fact table and a parameter table in the de fact table where I will return a different value according to the two data. I have a date in the column text parameters towards the lines in this parameter table. (For example, A, B, C, D, and E) So, in summary, I would like to do the same in Power Query as I did in Excel using Index and match. Could you please help?
Thanks Sir, Any book or concise material to read in 2021
@ExcellsFun Thank you for such a great trick, it has a little problem though. when running on big databases it becomes very very slow and not possible to extract back to Excel from Query, is there another way to achieve same result, from PowePivot Dax formulas for example?
Giorgi Tsomaia - I have the same problem, it seem to be working but "close and load" takes over night. I deleted the Table column but still slow go, not sure if it a one time process or every time a make a change to the query.
Hey! I work at Rolair!!! What are the chances that I would stumble across this video! CRAZY!!
Cool : )
I do all of these magic tricks at work and my boss is so confused. 😳
Confused in a happy way, I hope!!!!
My boss is happily confused.... :-)
@@NoShadowOfDoubt1 Nice : )
Hi Mike,
Thanks again, after rewatching I use this syntax to sort a nested table : Table.AddColumn(#"Grouped Rows1","all_sorted",(ot) => Table.Sort(ot[all],{{"Release Date", Order.Ascending}})), your videos are priceless! ;-)
Thanks for the tip, Eric, and I am glad that the videos are priceless fro you!
Great video!
I have a question: How can I check more than one column in your first example? For example: {[ProductID=[ProductID] or ... =....
What is the right way to write this code?
Thanks in advance
I have the same question, did you figure it out? thnx
Excellent video, Mr. Girbin. Now I have a question of CPU efficiency: At 09:19 you show the general formula for a one-way lookup, even if you have multiple lookup values (I would think that de '&&' operator works in this formula); with that in mind, one can think that the two-way lookup could replace Table.NestedJoin (JoinKind.LeftOuter). Do you think there is an efficiency cost involved in using two-way lookup or using Table.NestedJoin? Thanks in advance!
I have not tested this, but it is a good question. I will keep an eye out and try to notice if there is a difference. Also, && is an AND Logical Test Operator in DAX, but not Power Query.
@@excelisfun Hi Mike, thanks a lot. Have you got any further comment on the same question ( which runs faster, the Table.NestedJoin or Record.Field or List.PositionOf)? This I think will be extremely helpful to speed up power query refresh.
Thanks again.
In the exact match example, could you have unpivoted and then used a simple join based upon both fields? The second example is brilliant.
Yes indeed, you could : )
Hi. I am following these steps to bring in the value to one table from another table. ALl works well with a hard coded row #. The moment I add the look up steps, it tells me "There was an error deserializing the evaluation results. The operation might succeed on a retry, but it never succeeds regardless of how many times I retry. DO you have any advice on how to solve it?
Thanks Mike! My brain hurts. in a good way~
Hi guys
Thanks a lot for useful vid. But could you please kindly explain to me:
If the name of units from 2 tables are different. For example in f2ndUnits table Units turn to be Units sold and in ProductUnitsPrice Units is still unchanged. I don't understand this logic in this function since I have change the part from each table.Selectrows to be "each Table.SelectRows([ProductsUnitsPrice],each[Units]
I have a consolidated sheet, I want to bring the values from 2 different sheets, I use nested xlookup, but the problem is when I try to get the values from 2 tables and if the 2 different sheets have same lookup value, it is giving an error, how can I average the price and embeded in xlookup formula.
How can you combine data-entry columns with PQ lookups in the same table?
M is amazing. Is there anything it can't do?!
I think, maybe not... But there may things it can do, that might be more efficiently done with a different tool. Vise Versa too : )
Sir can you help me to know the function for ADD and SUBTRACT AUTOMATICALLY? For example i have a Stock =1000, then i Received=2000, in a press of a button it will update to 3000. Same as to Issue=500, in a press of a button it will update to 2500. Thanks a lot👍
Hi teacher, can i apply it when lookup price by date, example date 1 price is 10, date 15 price change to 14..
When I changes unit by date, query said: we cannot apply operator < to types datetime and date. Can u helpe??
Sir, I have some query on Pivot & Unpivot Powerquery. Could you please let me know how could I send the excel file?
I love power query! Any good books recommended for m code?
Not that I know of. Try MSPTDA Video #9 pdf notes. Those notes have most of what i know is a sort of logical order.
Can u even find a better source or book which gives more insight than this channel.... I bet u can't.... In that case learn freely and support this channel
RRR program Thanks ... I do support the channel religiously
@@rrrprogram8667 Thanks for your kind words!
@@rrrprogram8667 I have not seen a source for writing M Code anywhere, except the Power Query M language specification paper...
is the easiest way to do a ONE-way lookup in power query to just merge? or can the m code be used for this as well?
Merge is MUCH easier!!!!
I was wondering if I have a setting incorrect. When I add in a column and fill out the formula, the column fills in with the Record. However, when I click on the record to see the preview like how your shows at the bottom (9:57) mine goes to a new screen and I can not back up. Help...please.
What if you have multiple keys to match. Say Product ID and Region for example to pick the exact sales figure
add column with combined key id®ion to both tables and link tables on it
Its works, but my challenge is that i require the the price of a product at a specific date.
15+ years ago Lotus123 had @xindex to do two-way lookups. Here we are in 2019 with so many funky array formulas, M code, DAX etc. and Excel still needs vlookup/match lol
It is amazing how many tools we have now, and we are lucky that the worksheet has so many great lookup functions like VLOOKUP, MATCH and INDEX : ) Thanks for your consistent support on each video, Jamie!!!!
Hi Mike, I have problem using the formula :-VLOOKUP(f2ndUnits[@ProductID],ProductsUnitsPrice,MATCH(f2ndUnits[@Units],ProductsUnitsPrice[[#Headers],[0]:[4]]+0)+2,0). Despite putting the +0, excel thinks it is text instead of a number. Please help.
Did you enter the formula with Ctrl + Shift + Enter?
@@excelisfun Yes. I did. It gave me an answer as #N/A
Hi Mike,
I have solved the approximate match by other method using IF STATEMENT, would you please rate my solution :)
= Table.AddColumn(#"Changed Type", "Amount", each if [Units] >= 4 then Record.Field(ProductsUnitsPrice{[ProductID = [ProductID]]},"4") else if [Units] >= 2 and [Units] < 4 then Record.Field(ProductsUnitsPrice{[ProductID = [ProductID]]},"2") else if [Units] >= 0 and [Units] < 2 then Record.Field(ProductsUnitsPrice{[ProductID = [ProductID]]},"0") else 0 )
@ExcelIsFun
Hi Mike, I have a question. I have a code that works on a file with around 35K rows and I applied your method of two-way lookup in that code and it has rendered the code virtually useless. It takes forever for the code to run. Is there a workaround of doing the same thing that can be replicated for, say at least a million rows?
Best Regards,
Mueez
I think buffering the lookup table might help. Using Table.Buffer can help. Buffering the table allows Power Query to query to get table one time and than use it over and over, rather than having the query re-query the lookup table for each row in the helper column. You can add a step before the lookup column and use Table.Buffer, as seen in this video: ua-cam.com/video/e-NUgSgfnxw/v-deo.html use hyperlinked table of contents below this video to jump to the Table.Buffer section. And, although I have not tried this, I heard that some people just add a last step in the lookup table query and buffer the table there.
Let me know if this works or how you resolved it.
@@excelisfun Sir you are the best!!
I tried exactly what you told and although not perfect but it brought the time down from near infinity to around 30 seconds!
Your Fan since 2012 and counting :)
@@mueez89 If you have been a fan for 8 years, why have I not seen you comments until you need some help? Each video that you watch and learn, leave a comment, thumbs up, and of course I hope you are subbed : )
@@excelisfun I have been a subscriber ever since. 95% of all the excel that I know is through you. I also used to teach excel to first-year associates in my audit firm and would always mention your and Mr. Excel's channel. I loved the duels that you two used to have...
I just never thought you would ever reply to my comment let alone specifically advise me in the right direction!!
Power Query and Power Pivot were just a few things that I had back in time thought would be of no use to me so I dropped the idea of learning those. But just 4 years later they seem like the only thing relevant thing in the world!! Currently absorbing all of the super amazing MSPTDA series!!
Love you so much! Stay blessed and keep excelling!
P.S. I always do a thumbs up/rate 5 stars (back in the days) though I always felt these were no justice to what I actually learn through you. You are going to see a lot of comments from Mueez hereon! :)
Awesome mike... I feel one issue here.... When u unpivoted table at 14:06... We were lucky that... Units column was sorted from 0 to 4....
But if i already have unpivoted table with random arrangement... I guess we need to find another way to accomplish the task....
Only ExcelIsFun can do it... Coz i am out of touch with m code for quite some time :D
I have not seen too many Two-Way Lookup Tables that are not sorted in the first place... They would be meaningless then... But it could be done with M : )
Hello
How i navigate data from list under list in power query excell by using m formula language
Let
Source = {1,2,3,{"@","#","₹",{"a","b","c"}}}
in
source
Then make list under list but actually i want "a" or "#" how do i get it
Please help me
Hi
I am still waiting reply
Please reply
Thanks lots! Was searching fr sumifs and countifs alternative for the longest time ever and this kinda shed light unto it
.