VLOOKUP in Power Query Using List Functions - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
James
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.
reply
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.
reply
Will
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.
reply
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.
reply
Tam-s
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.
reply
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.
reply
Dave
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?
reply
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?
reply
Rodney
The timing of this is perfect as I am in the process of creating a data harmonization taxonomy from 4+ data sources. I need to lookup an attribute from the incoming data and match it to a taxonomy of attributes that I've created and output a taxonomy by product that is then used as a dim table in Power Pivot. WOW. THANKS.
reply
The timing of this is perfect as I am in the process of creating a data harmonization taxonomy from 4+ data sources. I need to lookup an attribute from the incoming data and match it to a taxonomy of attributes that I've created and output a taxonomy by product that is then used as a dim table in Power Pivot. WOW. THANKS.
reply
Giancarlo
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.
reply
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.
reply
Yogi
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
reply
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
reply
jonahviakeyboard
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.
reply
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.
reply
10ozGold
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).
reply
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).
reply
Saqib
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
reply
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
reply
Add a review, comment
Other channel videos
