VLOOKUP in Power Query Using List Functions
Вставка
- Опубліковано 28 лип 2021
- Using List functions in Power Query to do Exact Lookup and Approximate Match Lookup is faster and easier to create than using table merges.
Download the Excel file that includes the data and queries in this video: www.myonlinetraininghub.com/v...
0:15 Exact Match VLOOKUP
1:21 Using List.PositionOf to lookup value
2:32 Looking up the category for the corresponding value
3:15 Combining M code to reduce query steps
4:33 Approximate Match VLOOKUP
5:34 Using List.Select to lookup all matching values
7:55 Checking the content of lists created with List.Select
8:37 Using List.Count to lookup the approximate match value
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy - Наука та технологія
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!
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.
Wow! Jaw dropping awesome!
Thank you so much for creating/posting this video.
You're welcome Eric. Glad you enjoyed it.
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!
Elegant! Thanks, Phil and Mynda!
Thank you!
Wow!!! That is a lot easier than doing a merge. Thank You!!!
:) Glad it was helpful Mark
Thanks you both Mynda and Phil👍
Thanks Kebin
Nice video and a great tip yet again! thank you Mynda for sharing this! 😊👍
Our pleasure 😊
So easy to follow along! Great vid. Subscribed!
Thanks and 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!
I used this on a project at work the very next day! This is great
Great to hear 😊
Another great one! Thanks from South Africa.
Cheers Chris.
A really cool solution! Thank you.
No worries Pavol, glad you liked it.
Hi Mynda!Really Great And Helpful Tutorial From Phil..Thank You Both :)
Thanks Darryl. Glad you liked it.
This is valuable knowledge. Thank you for sharing.
My pleasure Adonis.
Good use of the 'let' statement to solve a range lookup problem
Cheers, Drew!
Mynda, You're quite amazing. Thanks for gathering this information for us.
You are so welcome! 🙏
Brilliant, exactly what I was looking for.
Great to hear!
Super Phil and Mynda...Thank you :)
Our pleasure, Vijay!
Great Tip!
Thanks for sharing :)
My pleasure, Vishal!
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, 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 😊
Yay just what I wanted thank u!🤩
No worries Taaha.
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 🙏
this is great... i use merges all the time! thank you (my computer thanks you too!!)
Glad you found it helpful 😊
Awesome, just what I needed.
😊 cheers, Jon!
Thats really very helpful, i implemented this technique on one of the project i am working in my company.
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 😊
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
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
Many thanks. Great help
You're welcome! 😊
Thank you, a useful skill )
Great to hear!
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?
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
Thanks a lot for this.
You're very welcome!
And this one is effective just awesome.
Great to hear!
Great video!! Is this method better or using combine query = merge better to save the file size/ refresh time?
Wow this is beyond amazing
Glad it was helpful!
It is a great solution ❤
Glad you found it helpful!
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!
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
Fantastic !!!!
Glad you like it!
Wow. Great video
Glad you liked it 😊
Well Done!
Thx Philip
This one worth more "Like" button.
Glad you think so!
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!
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!
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.
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.
Excellent 👌
Thanks for watching 😊
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.
Amazing, dude
Glad you liked it, Alvaro!
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 😊
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.
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
brain has not melted 😁excellent
That's a good start 😁
you are my saviour, merging the column in some cases are not efficient
Glad you liked 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'.
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.
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?
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!!!
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 😊
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?
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.
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.
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
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
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..
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
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.
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
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
Is merging makes the query more slow than doing lookup using list?
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.
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
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 😊
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
Helpful
Glad to hear that 😊
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.
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 _
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
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
Hello man it is possible to get slope results in power query
Probably, but I haven't tried it before.
Is this method work faster than merge step??
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
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.
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
Beautyfull
Thank you Luciano
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 :-)
Thanks for the tutorial but it doesn't work with direct query
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
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.
How do I compute Fifo method in power query
I don't have any examples I can point you to for FIFO.
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
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
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.
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
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.
too fast for me :P bur.....I try
Reach out if you get stuck: www.myonlinetraininghub.com/excel-forum