Could you explain me much more what is the purpose of the convention (IT), and (OT) when you attach them to some fiel such the case of IT[Units] and OT[Units]?
I have full free classes on M Code. Here is the video: ruclips.net/video/HRFZOYWGhjo/видео.html Custom functions and other topics including aprox match lookup are covered from the 01:12:35 minute mark to the 01:40:00 minute mark. The answer to your question is not easy because you need to know about custom functions and the different scope when you are nesting function's. In essence, you have two nested table functions each with a table that you are iterating, and so if you need a formula element to access the first function (outer table) and the second function (inside table), you must somehow define variables in each of the nested custom functions, or use the let expression. There are just so many concepts of M Code all in one formula, that if you are just trying to learn the formula, you can't learn it. You really need to study M Code from beginning to intermediate to get this formula. The above 1.5 hour video link teaches all of this if you are willing to study and try to assimilate.
@@excelisfun thanks mike, indeed i'm following ur series about data analysis MSPTDA, i ve watched the episode about M code, but i still found it hard to get this operation when you have nested tables and you try to iterate over it .. but thank any way, i just gonna try to uderstand it
For 13 years at RUclips, 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 ; )
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.
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! :-)
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
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!
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!
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 : ) : )
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!!!!!
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 :)
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!
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.
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 :)))
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 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 : )
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
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]
@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.
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)
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
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! ;-)
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 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.
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?
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.
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
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 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.
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?
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.
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👍
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 : )
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 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 )
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.
@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: ruclips.net/video/e-NUgSgfnxw/видео.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.
@@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! :)
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
Could you explain me much more what is the purpose of the convention (IT), and (OT) when you attach them to some fiel such the case of IT[Units] and OT[Units]?
I have full free classes on M Code. Here is the video:
ruclips.net/video/HRFZOYWGhjo/видео.html
Custom functions and other topics including aprox match lookup are covered from the 01:12:35 minute mark to the 01:40:00 minute mark.
The answer to your question is not easy because you need to know about custom functions and the different scope when you are nesting function's. In essence, you have two nested table functions each with a table that you are iterating, and so if you need a formula element to access the first function (outer table) and the second function (inside table), you must somehow define variables in each of the nested custom functions, or use the let expression.
There are just so many concepts of M Code all in one formula, that if you are just trying to learn the formula, you can't learn it. You really need to study M Code from beginning to intermediate to get this formula. The above 1.5 hour video link teaches all of this if you are willing to study and try to assimilate.
@@excelisfun thanks mike, indeed i'm following ur series about data analysis MSPTDA, i ve watched the episode about M code, but i still found it hard to get this operation when you have nested tables and you try to iterate over it ..
but thank any way, i just gonna try to uderstand it
@@othmanelebbache3026 The video link I sent is made 4 years after the MSPTDA one and is MUCH better. I promise, if you check it out: it will help!!
@@excelisfun so i ll do as u recommend, thank
Thank you for the 2-way lookup with M code :)
You are welcome, Teammate!!!
Which way the fastest to lookup ?
It is amazing that this knowledge is being shared at no direct cost to the viewer! Thank you!
For 13 years at RUclips, 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 ; )
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 : ) : )
"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.
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 : ) : )
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.
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?
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 : )
@ExcelIsFun comes through again (and again and again)! Thank you SO much!
You surely needs a thumbs up to the power of billion to this nice Video.
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!
I never ever ever ever comment on RUclips 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 : ) : )
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 : )
Thank you for showing both Exact Match and Approximate Match Lookup inside of Power Query! This is really great stuff!
Thanks Mike. Saved for the week end.
Yes, it is a lot, so some study time will be good : )
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!!!!!
This is out of this world kind of stuff.
Thanks a lot for this video. Comments are very clear and every steps are explained perfectly!! Thanks
You aer welcome!
Great job Mike!! And great assist by Bill
Thanks, Chris!!! Go team : )
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 : )
I can't ever find a teacher like you thank you Mr. Mike for your videos
Always glad to help, Ismail : )
Thank for uploading and sharing trick with us
You are welcome, Jawad!!!!!
Thanks for sharing Mike, and thanks to Bill S.
There are a lot of useful M code tips here!
Thanks Mike for these amazing formulas.
You are welcome, Syed : ) Thanks for your amazing support : ) : )
Approxitmate match in PQ - brilliant!
That amazing polish friend of yours - that Bill Szysz guy : )
Of course ;)
This tutorial has saved my time...thanks ;)
You are welcome, Rupal!!!
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 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 : )
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!!!
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 : )
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!
Very well explained :)
Glad you like it, Dipak! Thanks for the support !!
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.
you are god of excel.
Amazing lesson!
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 RUclips since 2008. If that is superhero like, then cool : )
Thanks Mike. Downloading and following step by step. : ) : )
Awesome Sat. Fun!!!!
My excel guru 🎉🙏!
This is just an amazing vid! Thank you!
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!
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 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 : )
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
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 : )
Amazing explanation of match lookup for M code
Glad you like it, Vida : )
You have just saved my day. Tks a lot!
You are welcome a lot, Thiago!!!!
Great video, as always! You inspire me to learn more and more! These M code videos are so powerful!
M Code is fun!!!!!
Hi Mike.. thanks for these great solutions. PQ M code is wild stuff! Thumbs up!!
You are welcome, Wayne : )
This was just brilliant, Mike. Can't thank you enough!
@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.
its awesome!!
Glad it is awesome for you, Anna : ) Thank you for your support with your comment, thumbs up and your Sub : )
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
Thanks Mike
Great! Is this more efficient on the refresh time than using the merge function?
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 : )
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)
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
Amazing!! Thanks so much
I'm speachless
Hey! I work at Rolair!!! What are the chances that I would stumble across this video! CRAZY!!
Cool : )
pioneer
Always glad to try and help!!!
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!
Very cool. Level up +++
@ExcellsFun any performance consideration if we are handling a large lookup table?
Thank you!
You are welcome!!!!
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 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.
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?
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 : )
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?
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...
Thanks Sir, Any book or concise material to read in 2021
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 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]
Hi teacher, can i apply it when lookup price by date, example date 1 price is 10, date 15 price change to 14..
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.
good
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!!!!
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?
Great vidéo. Thank you
You are welcome, sadyaz64!!!1 Thanks for your great support : )
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.
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👍
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.
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?
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 : )
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 : )
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!!!!
Thanks Mike! My brain hurts. in a good way~
Thanks lots! Was searching fr sumifs and countifs alternative for the longest time ever and this kinda shed light unto it
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 )
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
@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: ruclips.net/video/e-NUgSgfnxw/видео.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! :)
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
.