Many thanks for this excellent tutorial. The final formulation you show at 4:02 is very closely analogous to the venerable INDEX MATCH formulation for looking up across tables/ranges with a worksheet function.
Does the exact match list approach perform fast as a merge between data and categories? A merge does the same thing - I like the cleaner code of the list approach but the ease of the UI merge is an advantage - performance is the deciding factor.
It can depend on your data. How many rows? How many unique values? In my tests, using List functions is as fast if not faster than merging. You may need to use Buffer() functions on your lists to get the best performance.
This was worth watching, thanks. It might have been worth including doing an error trap if the List.PositionOf returns a -1, before doing a retrieval based on the index value
@@kons21 I finally figured this error out on my sheet. One of my columns was formatted as a number "123" and the other I was trying to match it to was formatted as general "ABC". I change the column formatted as general to number and finally got rid of the -1 error.
Thanks Phil& Mynda for the video. As a novice I found it quite difficult to follow because the excel file is the finished product and you jump a couple of stages in setting up the data set with queries which is fine for intermediate and advanced users but I'd have preferred a simpler approach starting with the raw data (I know its probably too basic for many but I'm a novice). Persevering I didn't find the list method particularly intuitive compared to non power query methods in excel so the video is one to refer back to.
Hi James, I understand what you're saying as you are not familiar with PQ, but loading a table into PQ is straightforward and I wouldn't want to go over that in every video I do. All you need to do is create a table in Excel by clicking into the data and then pressing CTRL+T. With the table created, and a cell in the table selected, go to your Ribbon -> Data -> From Table/Range. Or you can right click in the table and select Get data from Table/Range. In the PQ Editor you can then click on the Close & Load drop down (click the black arrow) in the Home tab and choose to Close & Load To .... then choose Connection Only. Regards Phil
Thanks for this awesome video. By using this answer, I have learned also that Power Query from Excel is case sensitive when using column names. Thanks a lot for this!
Same for me. I have exported the "CRM_Accounts" (29.789 rows) and the "CRM_Contacts" (38.729 rows) tables from MS Dynamics CRM and it takes well over 40 minutes to go through this. Meanwhile the memory utilization by Excel rose up to the 1.2 ~ 1.5 GB range, and the buffer reached already 4.84 GB when processing only 500 rows... I don't know the final numbers as I went away from my computer after 40 minutes to fix myself a drink. This very nice technique seems to be a memory bomb when dealing with larger tables, which is a pity, as I do like the method. Since the column in the All Contacts table contained a large number of null's I already tried to make it a bit more efficient with: = Table.AddColumn(#"Renamed Columns", "Sales Owner", each if [Account Number]=null then "" else CRM_Accounts[Sales Owner]{List.PositionOf(CRM_Accounts[Account Number], [Account Number])}) But I don't think it made any difference... @MyOnlineTrainingHub, any suggestions how to improve the performance or is the technique just not fit for larger (reference) tables?
Phil... great trick... List.PositionOf very elegant function to bring the ordinal position whitout adding an index number column , I will memorize this list function.
Hi Mynda and Phil. Thanks for this awesome video showing how to achieve a VLOOKUP using the power of list functions! Nice to know another way to do this vs. merging queries. Thanks for sharing :)) Thumbs up!!
Thanks Phil and Mynda - great video. What would the syntax be if the Categories table held both Category1 and Category2, and I needed both categories returned? Can your single step be expanded or would I need to essentially duplicate the Add Column? And, if we need a second Add Column (presumably requiring another search through Categories), would this mean that a Merge table would be more efficient?
Hi Dave, If you require 2 values to be returned I don't think you can avoid having 2 separate steps, and hence 2 lookups, to create 2 columns. Because the logic is to add the column, then do the lookup, rather then do the lookup and if you find it then add the column, you have to create the column first, requiring a step to do this. So in such a scenario would a merge be quicker? Maybe. I'd have to test with a representative sample of data. If you used List.Buffer() on your lists (columns) I'd expect that to boost performance. Phil
Thank you, thank you, thank you ... indexes fix nightmares. Dates are a massive pain across tables in Power Query. The might be a way using Min() > than x but I could not work out how to do it after hours and hours. Indexing the reference table +1 to get the next date worked fine. Awesome. Clear as clear.
Thanks! I've used the List.PositionOf method for lookups several times. But even on smaller lookups (say, 100 rows) I have real performance issues using the list function approach. My understanding is that PQ is deeply optimized for table operations, and merging queries brings those optimizations to bear. I will often see 5x performance improvement by using a merge. I am curious if you have some insights into the factors that might adversely affect using the list function.
I haven't done a lot of speed testing of the different list functions vs merges. I guess the list function might be indexing the whole dataset which is causing the load.
thanks for sharing! do you have a video showing a look up of a particular word from one table to another pls? i.e. ice cream, ice lolly, key word ice, show both of these.
Could this approach be used for big datasets? My PQ model is very complicated and slow because of a lot of merging, it takes >5h to refresh. So I always look for ways to speed it up. And yes, as someone wrote in another comment, it is important what happens when multiple values are found in look up.
Hi Saulius, If you are doing a lookup (exact presumably) you should only get 1 match. I covered a scenario in the video (approximate match) where multiple items are selected. Do you have an example of the type of multiple match you mean? I don't know any reason it wouldn't work on 'big' datasets (how big is big?) Without seeing your data and queries it's hard to give any definitive guidance as to improvements. You may get faster performance by combining List.Buffer() functions to create buffered lists and then using these to do your lookups.
Hey Saulius, I'm about to create a entire model with 7k rows and 50 cols in the main table, In your experience thiw could be a slow performance model? if does, then I would use other tool rather than PQ What do you think?
@@lopher70 It depends on calculations and other tables you are doing. Few tables with 7K would not be very slow model IMHO. Also it depends on your PC. My table has 60K rows and >50 columns, but I merge it with other >50K tables (queries). And I have ~100 queries merged.
Thank you !. I was looking for a solution to bring data from a Dataverse table into custom column in PowerBI inside a SQL table, both data sources being used in a PowerBI model.
Thanks Phil & Mynda, it is position to use the same method for composite keys ? for e.g. Name and Date, for merge we will just highlight both columns thanks !
Hi Mynda, always wonder how in the add custom column window, you can see the quick view of any formula/available column by simply type in the custom formula field. that would save a lot of time rather than have to type everything manually. thanks in advance
I’ve watched this multiple times - thanks so much. QQ - hopefully a simple one, is it possible to pull back more than one column in the add column?….I’ve tried to do this but it messed up so not sure how to work it or even if it’s possible?
Hi Kim, glad the video is helpful. I should think you can add more than one column. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda and Phil. Thanks for this awesome video. Is it possible to "upgrade" it to two-way lookup ? E.g. if the same Sales Persons are in two different Cities and for Cities are different Rates
There's no such thing as XLOOKUP in Power Query as such because Power Query doesn't have Excel Functions. There's lookups using List Functions, which this video covers and there's lookups with Merged tables, which this tutorial covers: www.myonlinetraininghub.com/excel-power-query-vlookup
Hi Mynda and Philip, excellent video about List.PositionOf. How do you use List functions for multiple column conditions? It's time for me to move away from the slow performing PQ Merge Queries interface (which does allow multiple column conditions).
@@philiptreacy714 Hi Philip, what if the [categories] had all duplicated rows, with an extra column, "Group", either as {"A", "B"}. Then [data] table also had an extra column "Group" {"A", "B"}. Now there are 2 conditions to lookup. Would a List function be possible? If so, this would be a game changer.
hello, i am new in power query. i need to use this strategy. but the thing is, my table (TABLE1) has used power query already loaded in 1 worksheet. on same workbook, I have added another table (TABLE2) in new worksheet forwhich one of its column need to match data and the get data to be added as another column to TABLE1. Ex. Table 1 with student name and grade needs to match with table 2 with name and sections. Result shall be in Table 1 with student name, grade, section as columns. I hope you can answer this. thanks much in advance.
It took me a long time to find this video. I should ask for a video of searching in your channel 🙂I have a table of 46K lines that I need to change some codes in it to their descriptions.
3:00 Couldn't you just map the Product to the Category and save the step of the index? You already have the table with the two lined up. Is there some special reason to use the index in this case?
You could do a table merge and map the Category to the Product, but what we wanted to show in this example is using List Functions to achieve the same result - without having to do table merges.
You either have it or you don't. If you don't have it then you'd have to upgrade to a later version of Excel. I'm not sure what version it came out in, but it's definitely available with a Microsoft 365 license.
Thank you so much for this great video! I applied it into my model it seems to be working but the query is extremely slow now and it takes really long time to refresh and load it. Is there a way to solve this issue? Thank you in advance!
That is amazing! Thanks! But how we do where condition is "Exact match. If none found, return the next larger item". In excel we use xlookup but how we do in power query?
This was just what I was looking for. I didn't want to create multiple "merge" tables to use when having multiple index/reference tables (e.g. geographic region, category, etc.). I was going to merge "data" table to "index1" table to create "merge1" table, and then "merge 1" table with "index2" table to create "merge2" table, etc. I AM concerned with handing this off to someone else, though, because I don't often encounter users of the M language of power query at work. A short primer should be fine.
Hi, thank you for this video, it was really helpful! I came across -1 values which meant they were not found but PowerQuery has labeled them as "Error." Is there anyway I handle this so that instead of displaying "Error" it should say "Not found"? Please can you direct me to where I can find a solution to this?
Thanks for this video! I want just ask you a question! How do the functions apear while you taped the code on the box.. it deosn't work in my power query.. Thanks for your Nice job
Hi, It's the Intellisense. Yours must be turned off. In the Power Query editor, go to File -> Options and Setings -> Query Options -> GLOBAL -> Power Query Editor and under Formula (on the right hand side of the window), make sure 'Enable M Intellisense ...' is checked.
I have a list of employees and each has individual rates standard overtime rates which the timecards. Tell the rate to use looking for a simply way to return the rates. Any examples to look at please.
Lookups sound like the way to go. If you're stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi Dino, List.PositionOf works in a similar way but doesn't require numeric values to be sorted like MATCH does when looking for largest or smallest values..
Would this improve performance? I have had issues using merge when the 2 tables being merged are one to one (the same record is on both tables but one of the tables has additional info which I need to incorporate). Both tables are about 500 K records big and when I try to merge them, PQ slows to a crawl.
@@MyOnlineTrainingHub I am new to power query wondering why not use merge for the first part to get the category, could you please explain the pros of using this method and cons of using merge, thanks in advance really leran a lot from you videos 😊
the logic workes but it subtantially increases the query load time to a point were if the data set is big then the query almost always times out, is there a work around for it.
Hi Kumar, I don't know any reason it wouldn't work on 'large' datasets (how large is large?) I haven't optimised this in any way. I'd expect you'd get better performance by using the List.Buffer() function to create buffered lists that can then be fed into the List functions doing the lookups.
This is really good ... PQ has a lot of very interesting and useful function that most of the people don´t know. It is great to have someone who could explain how they work. Do you have any test comparing the performance of two methods?
Many to many is only possible in Power BI. I would set up a dimension table for your customers and create a many to one relationship between the customers sales and payment tables to the customers dimension table.
hey mynda the list.position function is taking more time thatn it would take in mergin the query is it supposed to be like that or am i doing something wrong could you please let me know?
Not sure if you're referring to the query lines of code or the rows of data in the query. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello!!! Greteengs from Colombia. I have a question, how can i do the same vlookup but conditional? For example, if condittion1 is ok then put in the cell "Ok" else go to another table and to do the same vlookup for to do another search.. Thankssss
Great Video. I have a question in my work area. Is it possible to do the same, but not from a table, but from the end result of another step in the same query? Let me give you an example: I have a table. In the next steps, I perform calculations with the Group By function, filters, etc. In the next step, I recall the basic table again and want to insert the results of the calculations into the related rows of the table in a vlookup-like manner.
@@MyOnlineTrainingHub Thx your answer :) 151 / 5 000 I knew this solution, it just increased the file size due to the duplicate query and interested optimization reasons to see if it could be within a query.
My vlookup doesn't work on the values extracted through mid formula. when i copy-paste these values, means removing the mid formula then vlookup does work. i change the format to numbers as well but didn't get fix the issue. Can you help me, please
Hi Munda and Phile. Thanks for the great content. I have a query, please help. What if the lookup table has two columns one is for range and the other one is for sales (a calculated column). I want to rank the sales basis the range using approximate match in another table. Please help it's urgent 🥺
Great video, especially the combining of several steps into one, this often gets glossed over. I'm guessing that the approximate match with the assigned variable couldn't be combined in a single step?
Thx Rico. Yes those steps could be combined (maybe I should have shown that in the video!) You'd need just 1 step - create a Custom Column with this code BonusRates[Rate]{List.Count( let val = [Sales] in List.Select( BonusRates[Threshold] , each _
Great tutorial... I'm sure I'm making a rookie error, but I have two tables [Product] and [Seasonal_Lookup]. Like you, I made Seasonal_Lookup connection only. Seasonal_Lookup has two Columns and I created the command " = Table.AddColumn(#"Add CL1 Margin", "Custom", each List.PositionOf(Seasonal_Lookup[Collection_Tag],[Title])) " . The one thing I have noticed and am getting stuck on is ... When I Enter the Power Query in the editor, it finds " Seasonal_Lookup " as a value I can insert, but, it can't seem to find the Column " [Collection_Tag] " within the table. I have a second Column names "Season" and I've tried that too. Is there some trick to having Power Query find the Column Header by name? it definitely shows as a column Header, but I've tried everything to try to address it and can't seem to. thanks
It's difficult to picture your problem, however it's important to understand that Power Query is case sensitive, so it will be looking for an exact match on any reference to column names. If you're still stuck please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I am still fairly new at power query but i do use merge queries quite a bit in my work and i find it really easy to use where i'd have to watch this tutorial several times to get it. but if there is a real beneft, i shall do it
i tried this approach....though it reduces the number of applied steps, it takes a lot of time when i refresh the preview.....thats a major problem ..... merge happens in seconds.....am i missing anything here. any suggestions ??
This is not working so great for me. I have 10.000 rows of data and it take several minutes to complete the calculation. I guess i stick to joining the tables.
Greeting madam, I tray to follow all of your vedio but due to enough time I can't follow all vedio. I have a request to you to make a vedio on material management specially in mechanical section.
You can use the table merge approach if you wish as explained in these posts www.myonlinetraininghub.com/excel-power-query-vlookup www.myonlinetraininghub.com/power-query-approximate-match-vlookup
Hi Dayve, not quite sure what you mean. If you're doing a lookup, you'll only be looking up in 1 table? If you need to lookup the same value in multiple tables then you'll do a lookup for each table. Or consolidate lookup tables into 1 table and then do a single lookup returning multiple columns.
Hard to say, Matheus. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
With Power Query you can bring data into Excel from multiple sources, perform the lookup and then load just one table into Excel with no formulas. This is far more efficient than having a load of excess data and thousands of formulas in your file. Hope that helps clarify.
Many thanks for this excellent tutorial. The final formulation you show at 4:02 is very closely analogous to the venerable INDEX MATCH formulation for looking up across tables/ranges with a worksheet function.
Glad you liked it!
Great video! exactly what I was looking for! Better than merging the columns! Thank you very much!
You're welcome Maria.
This is awesome! Saved in my tutorials list to use very soon. Thank you Mynda and Phil!
Great to hear, Celia!
Does the exact match list approach perform fast as a merge between data and categories? A merge does the same thing - I like the cleaner code of the list approach but the ease of the UI merge is an advantage - performance is the deciding factor.
It can depend on your data. How many rows? How many unique values? In my tests, using List functions is as fast if not faster than merging. You may need to use Buffer() functions on your lists to get the best performance.
@@MyOnlineTrainingHub any videos on buffer use?
Good use of the 'let' statement to solve a range lookup problem
Cheers, Drew!
This was worth watching, thanks. It might have been worth including doing an error trap if the List.PositionOf returns a -1, before doing a retrieval based on the index value
Nice idea. Thanks for sharing, Walter 🙏
@@MyOnlineTrainingHubthis is exactly what's happening to me. I get -1 on all of my rows. Any idea why that's happening?
@@kons21 I finally figured this error out on my sheet. One of my columns was formatted as a number "123" and the other I was trying to match it to was formatted as general "ABC". I change the column formatted as general to number and finally got rid of the -1 error.
Thanks Phil& Mynda for the video. As a novice I found it quite difficult to follow because the excel file is the finished product and you jump a couple of stages in setting up the data set with queries which is fine for intermediate and advanced users but I'd have preferred a simpler approach starting with the raw data (I know its probably too basic for many but I'm a novice). Persevering I didn't find the list method particularly intuitive compared to non power query methods in excel so the video is one to refer back to.
Hi James,
I understand what you're saying as you are not familiar with PQ, but loading a table into PQ is straightforward and I wouldn't want to go over that in every video I do.
All you need to do is create a table in Excel by clicking into the data and then pressing CTRL+T.
With the table created, and a cell in the table selected, go to your Ribbon -> Data -> From Table/Range. Or you can right click in the table and select Get data from Table/Range.
In the PQ Editor you can then click on the Close & Load drop down (click the black arrow) in the Home tab and choose to Close & Load To .... then choose Connection Only.
Regards
Phil
I used this on a project at work the very next day! This is great
Great to hear 😊
Elegant! Thanks, Phil and Mynda!
Thank you!
Thanks you both Mynda and Phil👍
Thanks Kebin
Wow!!! That is a lot easier than doing a merge. Thank You!!!
:) Glad it was helpful Mark
Mynda, You're quite amazing. Thanks for gathering this information for us.
You are so welcome! 🙏
Thanks for this awesome video. By using this answer, I have learned also that Power Query from Excel is case sensitive when using column names. Thanks a lot for this!
Glad it was helpful!
Thanks. I found it takes so long to refresh for a 20K lines data.
Hmmm, that's not ideal.
Same for me. I have exported the "CRM_Accounts" (29.789 rows) and the "CRM_Contacts" (38.729 rows) tables from MS Dynamics CRM and it takes well over 40 minutes to go through this.
Meanwhile the memory utilization by Excel rose up to the 1.2 ~ 1.5 GB range, and the buffer reached already 4.84 GB when processing only 500 rows...
I don't know the final numbers as I went away from my computer after 40 minutes to fix myself a drink.
This very nice technique seems to be a memory bomb when dealing with larger tables, which is a pity, as I do like the method.
Since the column in the All Contacts table contained a large number of null's I already tried to make it a bit more efficient with:
= Table.AddColumn(#"Renamed Columns", "Sales Owner", each if [Account Number]=null then "" else CRM_Accounts[Sales Owner]{List.PositionOf(CRM_Accounts[Account Number], [Account Number])})
But I don't think it made any difference...
@MyOnlineTrainingHub, any suggestions how to improve the performance or is the technique just not fit for larger (reference) tables?
Phil... great trick... List.PositionOf very elegant function to bring the ordinal position whitout adding an index number column , I will memorize this list function.
Glad it was helpful Jazzista
Thank you for this simple look up. I was able to solve my problem
You're welcome! Glad it helped.
this is great... i use merges all the time! thank you (my computer thanks you too!!)
Glad you found it helpful 😊
Hi Mynda and Phil. Thanks for this awesome video showing how to achieve a VLOOKUP using the power of list functions! Nice to know another way to do this vs. merging queries. Thanks for sharing :)) Thumbs up!!
Thanks Wayne, glad it was helpful.
This is EXACTLY what i was searching for. Every other answer I could find involved merging tables which is not the same thing
Glad I could help, Tony 😊
Another great one! Thanks from South Africa.
Cheers Chris.
Super Phil and Mynda...Thank you :)
Our pleasure, Vijay!
So easy to follow along! Great vid. Subscribed!
Thanks and welcome!
Brilliant, exactly what I was looking for.
Great to hear!
This one worth more "Like" button.
Glad you think so!
Thanks Phil and Mynda - great video. What would the syntax be if the Categories table held both Category1 and Category2, and I needed both categories returned? Can your single step be expanded or would I need to essentially duplicate the Add Column?
And, if we need a second Add Column (presumably requiring another search through Categories), would this mean that a Merge table would be more efficient?
Great question ...... will be interested to see the reply to this one
Hi Dave,
If you require 2 values to be returned I don't think you can avoid having 2 separate steps, and hence 2 lookups, to create 2 columns. Because the logic is to add the column, then do the lookup, rather then do the lookup and if you find it then add the column, you have to create the column first, requiring a step to do this.
So in such a scenario would a merge be quicker? Maybe. I'd have to test with a representative sample of data. If you used List.Buffer() on your lists (columns) I'd expect that to boost performance.
Phil
@@MyOnlineTrainingHub Thanks Phil
Thank you, thank you, thank you ... indexes fix nightmares. Dates are a massive pain across tables in Power Query. The might be a way using Min() > than x but I could not work out how to do it after hours and hours. Indexing the reference table +1 to get the next date worked fine. Awesome. Clear as clear.
Glad it was helpful 😊
Thats really very helpful, i implemented this technique on one of the project i am working in my company.
Glad it was helpful!
Thanks! I've used the List.PositionOf method for lookups several times. But even on smaller lookups (say, 100 rows) I have real performance issues using the list function approach. My understanding is that PQ is deeply optimized for table operations, and merging queries brings those optimizations to bear. I will often see 5x performance improvement by using a merge. I am curious if you have some insights into the factors that might adversely affect using the list function.
I haven't done a lot of speed testing of the different list functions vs merges. I guess the list function might be indexing the whole dataset which is causing the load.
thanks for sharing! do you have a video showing a look up of a particular word from one table to another pls? i.e. ice cream, ice lolly, key word ice, show both of these.
Great video!! Is this method better or using combine query = merge better to save the file size/ refresh time?
Wow! Jaw dropping awesome!
Thank you so much for creating/posting this video.
You're welcome Eric. Glad you enjoyed it.
A really cool solution! Thank you.
No worries Pavol, glad you liked it.
Great Tip!
Thanks for sharing :)
My pleasure, Vishal!
This is valuable knowledge. Thank you for sharing.
My pleasure Adonis.
Awesome, just what I needed.
😊 cheers, Jon!
Could this approach be used for big datasets? My PQ model is very complicated and slow because of a lot of merging, it takes >5h to refresh. So I always look for ways to speed it up. And yes, as someone wrote in another comment, it is important what happens when multiple values are found in look up.
Hi Saulius,
If you are doing a lookup (exact presumably) you should only get 1 match. I covered a scenario in the video (approximate match) where multiple items are selected. Do you have an example of the type of multiple match you mean?
I don't know any reason it wouldn't work on 'big' datasets (how big is big?)
Without seeing your data and queries it's hard to give any definitive guidance as to improvements. You may get faster performance by combining List.Buffer() functions to create buffered lists and then using these to do your lookups.
Hey Saulius, I'm about to create a entire model with 7k rows and 50 cols in the main table,
In your experience thiw could be a slow performance model?
if does, then I would use other tool rather than PQ
What do you think?
@@lopher70 It depends on calculations and other tables you are doing. Few tables with 7K would not be very slow model IMHO. Also it depends on your PC. My table has 60K rows and >50 columns, but I merge it with other >50K tables (queries). And I have ~100 queries merged.
Thank you !. I was looking for a solution to bring data from a Dataverse table into custom column in PowerBI inside a SQL table, both data sources being used in a PowerBI model.
Hope it was helpful!
Hi Mynda!Really Great And Helpful Tutorial From Phil..Thank You Both :)
Thanks Darryl. Glad you liked it.
Thanks Phil & Mynda, it is position to use the same method for composite keys ? for e.g. Name and Date, for merge we will just highlight both columns thanks !
Hi Christopher, yes you can do this but you'd need to create a new column that combines the Name and Date into a unique identifier.
Hi Mynda, always wonder how in the add custom column window, you can see the quick view of any formula/available column by simply type in the custom formula field.
that would save a lot of time rather than have to type everything manually.
thanks in advance
Great suggestion!
I’ve watched this multiple times - thanks so much. QQ - hopefully a simple one, is it possible to pull back more than one column in the add column?….I’ve tried to do this but it messed up so not sure how to work it or even if it’s possible?
Hi Kim, glad the video is helpful. I should think you can add more than one column. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
you are my saviour, merging the column in some cases are not efficient
Glad you liked it 😊
Nice video and a great tip yet again! thank you Mynda for sharing this! 😊👍
Our pleasure 😊
Hi Mynda and Phil. Thanks for this awesome video.
Is it possible to "upgrade" it to two-way lookup ?
E.g. if the same Sales Persons are in two different Cities and for Cities are different Rates
Thank you for taking up this topic Mynda! Can you please also share a video on XLOOKUP in power query!
There's no such thing as XLOOKUP in Power Query as such because Power Query doesn't have Excel Functions. There's lookups using List Functions, which this video covers and there's lookups with Merged tables, which this tutorial covers: www.myonlinetraininghub.com/excel-power-query-vlookup
Wow this is beyond amazing
Glad it was helpful!
And this one is effective just awesome.
Great to hear!
Hi Mynda and Philip, excellent video about List.PositionOf. How do you use List functions for multiple column conditions? It's time for me to move away from the slow performing PQ Merge Queries interface (which does allow multiple column conditions).
Hi, can you give me an example of what you are trying to do ?
@@philiptreacy714 Hi Philip, what if the [categories] had all duplicated rows, with an extra column, "Group", either as {"A", "B"}. Then [data] table also had an extra column "Group" {"A", "B"}. Now there are 2 conditions to lookup. Would a List function be possible? If so, this would be a game changer.
hello, i am new in power query. i need to use this strategy. but the thing is, my table (TABLE1) has used power query already loaded in 1 worksheet. on same workbook, I have added another table (TABLE2) in new worksheet forwhich one of its column need to match data and the get data to be added as another column to TABLE1.
Ex. Table 1 with student name and grade needs to match with table 2 with name and sections. Result shall be in Table 1 with student name, grade, section as columns.
I hope you can answer this. thanks much in advance.
Please post your question on our Excel forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Is merging makes the query more slow than doing lookup using list?
It took me a long time to find this video. I should ask for a video of searching in your channel 🙂I have a table of 46K lines that I need to change some codes in it to their descriptions.
Glad you found us eventually 😊
Many thanks. Great help
You're welcome! 😊
3:00 Couldn't you just map the Product to the Category and save the step of the index? You already have the table with the two lined up. Is there some special reason to use the index in this case?
You could do a table merge and map the Category to the Product, but what we wanted to show in this example is using List Functions to achieve the same result - without having to do table merges.
How do you enable the hint bar when typing out the formulas in the custom column?
You either have it or you don't. If you don't have it then you'd have to upgrade to a later version of Excel. I'm not sure what version it came out in, but it's definitely available with a Microsoft 365 license.
Thank you so much for this great video! I applied it into my model it seems to be working but the query is extremely slow now and it takes really long time to refresh and load it. Is there a way to solve this issue? Thank you in advance!
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub I just saw your reply. I will post the question in the link. Thanks!!!
Yay just what I wanted thank u!🤩
No worries Taaha.
It is a great solution ❤
Glad you found it helpful!
That is amazing! Thanks! But how we do where condition is "Exact match. If none found, return the next larger item". In excel we use xlookup but how we do in power query?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thanks. Hi after registrations, unable to login to post the question.
@@MyOnlineTrainingHub Query is Post this forum. thanks.
This was just what I was looking for. I didn't want to create multiple "merge" tables to use when having multiple index/reference tables (e.g. geographic region, category, etc.). I was going to merge "data" table to "index1" table to create "merge1" table, and then "merge 1" table with "index2" table to create "merge2" table, etc.
I AM concerned with handing this off to someone else, though, because I don't often encounter users of the M language of power query at work. A short primer should be fine.
Glad it was helpful, Will!
Hi. How do you activate the "formula/function assist" to show available functions/tables as he was typing it?
It’s available in later versions of Excel. You’d need to upgrade to get it.
Hi, thank you for this video, it was really helpful!
I came across -1 values which meant they were not found but PowerQuery has labeled them as "Error." Is there anyway I handle this so that instead of displaying "Error" it should say "Not found"? Please can you direct me to where I can find a solution to this?
Insert a step to replace the -1 values with 'Not found'.
Thanks a lot for this.
You're very welcome!
Thanks for this video!
I want just ask you a question!
How do the functions apear while you taped the code on the box.. it deosn't work in my power query..
Thanks for your Nice job
Hi,
It's the Intellisense. Yours must be turned off. In the Power Query editor, go to File -> Options and Setings -> Query Options -> GLOBAL -> Power Query Editor and under Formula (on the right hand side of the window), make sure 'Enable M Intellisense ...' is checked.
Thank you for this detailed video on how to replace vlookup using Power Query. Is it possible to make this process into a query function?
Yes you can!
Thank you 😊
How can this be used for just filling the blanks in column using reference og any of the column like a lookup... Just for blank values only
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I have a list of employees and each has individual rates standard overtime rates which the timecards. Tell the rate to use looking for a simply way to return the rates. Any examples to look at please.
Lookups sound like the way to go. If you're stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Excellent 👌
Thanks for watching 😊
brain has not melted 😁excellent
That's a good start 😁
Hi, Very good. List.PositionOf function is like Match in excel? Correct?
Hi Dino,
List.PositionOf works in a similar way but doesn't require numeric values to be sorted like MATCH does when looking for largest or smallest values..
Would this improve performance? I have had issues using merge when the 2 tables being merged are one to one (the same record is on both tables but one of the tables has additional info which I need to incorporate). Both tables are about 500 K records big and when I try to merge them, PQ slows to a crawl.
Hi Giancarlo,
quite possibly with some tweaks. Can you post your data on our forum and I'll take a look at it.
Phil
@@MyOnlineTrainingHub I am new to power query wondering why not use merge for the first part to get the category, could you please explain the pros of using this method and cons of using merge, thanks in advance really leran a lot from you videos 😊
Wow. Great video
Glad you liked it 😊
Well Done!
Thx Philip
Thanks for the tutorial but it doesn't work with direct query
the logic workes but it subtantially increases the query load time to a point were if the data set is big then the query almost always times out, is there a work around for it.
Try using the List.Buffer function to store the list(s) in memory and you should see a speed improvement.
Is this method work faster than merge step??
Will it work on large datasets?
Is this an optimized solution for looking up value on large datasets
Hi Kumar,
I don't know any reason it wouldn't work on 'large' datasets (how large is large?)
I haven't optimised this in any way. I'd expect you'd get better performance by using the List.Buffer() function to create buffered lists that can then be fed into the List functions doing the lookups.
This is really good ... PQ has a lot of very interesting and useful function that most of the people don´t know. It is great to have someone who could explain how they work. Do you have any test comparing the performance of two methods?
Thx Gilberto. Yes I have some test data/code but it's in a really ugly file. Let me tidy it up before sharing.
@@MyOnlineTrainingHubwas there a large performance gain?
hi
how we can relations between many to many ?
for example table of customers sales and table payment customers table ?
thanks
Many to many is only possible in Power BI. I would set up a dimension table for your customers and create a many to one relationship between the customers sales and payment tables to the customers dimension table.
@@MyOnlineTrainingHub thanks 🙏
I appreciate your help
hey mynda the list.position function is taking more time thatn it would take in mergin the query is it supposed to be like that or am i doing something wrong could you please let me know?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Fantastic !!!!
Glad you like it!
Amazing, dude
Glad you liked it, Alvaro!
Hello man it is possible to get slope results in power query
Probably, but I haven't tried it before.
Hello, I need to replace a line from a query with a set of corresponding lines from another query. How to do this?
Not sure if you're referring to the query lines of code or the rows of data in the query. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub thanks ;)
Hello!!! Greteengs from Colombia. I have a question, how can i do the same vlookup but conditional? For example, if condittion1 is ok then put in the cell "Ok" else go to another table and to do the same vlookup for to do another search..
Thankssss
Hi Sergio, please see this tutorial: www.myonlinetraininghub.com/power-query-if-statements
Great Video. I have a question in my work area. Is it possible to do the same, but not from a table, but from the end result of another step in the same query? Let me give you an example: I have a table. In the next steps, I perform calculations with the Group By function, filters, etc. In the next step, I recall the basic table again and want to insert the results of the calculations into the related rows of the table in a vlookup-like manner.
Glad you liked it :-) Just duplicate the query, use one for your grouping and the other to bring the grouped data into the original table.
@@MyOnlineTrainingHub Thx your answer :) 151 / 5 000
I knew this solution, it just increased the file size due to the duplicate query and interested optimization reasons to see if it could be within a query.
My vlookup doesn't work on the values extracted through mid formula. when i copy-paste these values, means removing the mid formula then vlookup does work. i change the format to numbers as well but didn't get fix the issue. Can you help me, please
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Munda and Phile. Thanks for the great content. I have a query, please help. What if the lookup table has two columns one is for range and the other one is for sales (a calculated column). I want to rank the sales basis the range using approximate match in another table. Please help it's urgent 🥺
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great video, especially the combining of several steps into one, this often gets glossed over. I'm guessing that the approximate match with the assigned variable couldn't be combined in a single step?
Thx Rico. Yes those steps could be combined (maybe I should have shown that in the video!)
You'd need just 1 step - create a Custom Column with this code
BonusRates[Rate]{List.Count( let val = [Sales] in List.Select( BonusRates[Threshold] , each _
Great tutorial... I'm sure I'm making a rookie error, but I have two tables [Product] and [Seasonal_Lookup]. Like you, I made Seasonal_Lookup connection only. Seasonal_Lookup has two Columns and I created the command " = Table.AddColumn(#"Add CL1 Margin", "Custom", each List.PositionOf(Seasonal_Lookup[Collection_Tag],[Title])) " . The one thing I have noticed and am getting stuck on is ... When I Enter the Power Query in the editor, it finds " Seasonal_Lookup " as a value I can insert, but, it can't seem to find the Column " [Collection_Tag] " within the table. I have a second Column names "Season" and I've tried that too. Is there some trick to having Power Query find the Column Header by name? it definitely shows as a column Header, but I've tried everything to try to address it and can't seem to. thanks
It's difficult to picture your problem, however it's important to understand that Power Query is case sensitive, so it will be looking for an exact match on any reference to column names. If you're still stuck please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
what's the benefit of this compared to merging queries?
I am still fairly new at power query but i do use merge queries quite a bit in my work and i find it really easy to use where i'd have to watch this tutorial several times to get it. but if there is a real beneft, i shall do it
i tried this approach....though it reduces the number of applied steps, it takes a lot of time when i refresh the preview.....thats a major problem ..... merge happens in seconds.....am i missing anything here. any suggestions ??
Hi, Try wrapping your lists in List.Buffer
how to do this if we need to look for more than one column?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
How do I compute Fifo method in power query
I don't have any examples I can point you to for FIFO.
This is not working so great for me. I have 10.000 rows of data and it take several minutes to complete the calculation. I guess i stick to joining the tables.
That's a shame. I agree, do what works best for your scenario.
Greeting madam,
I tray to follow all of your vedio but due to enough time I can't follow all vedio.
I have a request to you to make a vedio on material management specially in mechanical section.
Thanks for the suggestion :-)
How about using merge instead of list function?
You can use the table merge approach if you wish as explained in these posts
www.myonlinetraininghub.com/excel-power-query-vlookup
www.myonlinetraininghub.com/power-query-approximate-match-vlookup
This only works on a 1 to 1 right? 1 to Many does not work from what I can see.
Hi Dayve, not quite sure what you mean. If you're doing a lookup, you'll only be looking up in 1 table? If you need to lookup the same value in multiple tables then you'll do a lookup for each table. Or consolidate lookup tables into 1 table and then do a single lookup returning multiple columns.
When I search for the column on the other table, it doesn't find the columns.
What am I doing wrong ?
Hard to say, Matheus. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Helpful
Glad to hear that 😊
I am not understanding the benefit of using power query in this example. If we use vlookup(), it's much quicker to find the answers.
With Power Query you can bring data into Excel from multiple sources, perform the lookup and then load just one table into Excel with no formulas. This is far more efficient than having a load of excess data and thousands of formulas in your file. Hope that helps clarify.