Thanks for the video. Very well explained. I write a lot of queries, but this will definitely help me simplify them and save time. You are definitely the best Excel teacher in RUclips.
Very creative solution, hadn't thought about that yet! I'm also interested in knowing if there are some performance benefits of method 2 (or 3) compared to a normal merge. I'll try to do some testing myself too. Will also check out what happens if you have multiple matches in the results. Thanks for another great suggestion!
This is first thing came to my mind, since using merging I believe there is some performance issue especially when u have a large data set to scan through, definitely I ll check this method
Nicely explained thank you. I like the change type added to the end of the step. I’ve been doing that as a final step and removing earlier ones as I’m not overly confident writing code myself yet. I was also wondering about if this method has any speed benefits over merge.
I'm going to try to perform some test to see if one way is better than the other. My guess is that it depends on the data sources. Thanks for your support.
@@bcti-bcti I was thinking about the same. Would be interesting to see the results of such tests. One of recent videos (don't remember the author) showed that merge as the quickest function when dealing with large datasets for filtering where one filters out through merger anti. Thanks
Thank you for this. Very well timed as I am currently working on a query with a lot of "lookups." After 2 different merge steps, a filter using a list, custom columns using if statements to return true/false (another kind of lookup), the query is starting to take too long to execute. All that to ask: When is it better to use List.PositionOf vs. a Merge?
I think it comes down to the data sources and the number of records. You should always perform some timing/performance tests using both methods to see which performs better or worse. One might be better in Situation A, but fair worse in Situation B. Thanks for watching.
Agreed with most people here, very easy for the excel users here to understand and transition to power query. This is an exact match, do we have alternative for non exact match? Thanks!
I have a business case that I send the sheet to 4 persons, then the main sheet with me . i do vlookup to get the values in a specific column from the 4 persons is there anyway to do a vlookup but in the power query from the 4 sheets?
what if if need to lookup to two differente values? what would be the solution in that case? is there any & option and wildcard like in EXCEL? thanks in advance!
Power Query does not natively support wildcards. However, below is a link to an article on mimicking the behavior as close as possible: gorilla.bi/power-query/wildcards/
Hi Professor, I notice that if I use "List.PositionOf" and it doesn't find the value, it will return a (-1), so, therefore the lookup returns an error. I could add another step to replace the errors, but if you find a walkaround to avoid another step, will be helpful. Thanks in advance for your feedback.
Like seriously the way you teach is just amazing! It rare to find a diamond like you!! (seriously)
That is such a sweet and heartwarming thing for you to say. Thank you!
Can't agree more.
@@chandrakanthagarwal1174 Thank you!
This is interesting because the position ID acts like a unique table ID. I definitely have lots of uses for this, thank you.
@@Acheiropoietos Yes, it does. Many uses are possible. Glad it is helpful to you.
Thanks for the video. Very well explained. I write a lot of queries, but this will definitely help me simplify them and save time. You are definitely the best Excel teacher in RUclips.
Awwww. That is so nice of you to say! Thank you so much.
Wow, all the forms you presented are very well explained, very clear! Thank you very much for your classes. Thanks a lot!
Happy to have been of service!
Thanks, Professor! As usual, great content.
Thank you, great student!
Thank you, this technique will simplify a query I recently wrote!
It's so nice to hear when a video has immediate benefit! Glad it helped.
Interesting way to solve it. Thank you very much for sharing!!!
You are welcome!
Another great video, thank you!
Glad you enjoyed it!
Very nice video.
Thank you very much!
Fantastic explanation...
Thank You!!!! We appreciate your support.
Very creative solution, hadn't thought about that yet! I'm also interested in knowing if there are some performance benefits of method 2 (or 3) compared to a normal merge. I'll try to do some testing myself too. Will also check out what happens if you have multiple matches in the results. Thanks for another great suggestion!
This is first thing came to my mind, since using merging I believe there is some performance issue especially when u have a large data set to scan through, definitely I ll check this method
Performance will likely change depending on the data sources involved. But it's cool to know, if nothing else.
Thank YOU for your support. Let us know what you discover.
@@bcti-bcti I actually tried it, and trust me, sticking to merging queries is way better. When I tested this, it took around 10 times longer.
@@scotolivera8207 Wow! That's good to know. What were the data sources?
Nicely explained thank you. I like the change type added to the end of the step. I’ve been doing that as a final step and removing earlier ones as I’m not overly confident writing code myself yet. I was also wondering about if this method has any speed benefits over merge.
I'm going to try to perform some test to see if one way is better than the other. My guess is that it depends on the data sources. Thanks for your support.
Nice solution. Any idea if there is any performance advantage to either method?
I'll try to do some testing in the next few days to see if there is any difference from a speed or model size perspective. Thanks for watching.
@@bcti-bcti I was thinking about the same. Would be interesting to see the results of such tests. One of recent videos (don't remember the author) showed that merge as the quickest function when dealing with large datasets for filtering where one filters out through merger anti. Thanks
Thank you for this. Very well timed as I am currently working on a query with a lot of "lookups." After 2 different merge steps, a filter using a list, custom columns using if statements to return true/false (another kind of lookup), the query is starting to take too long to execute. All that to ask: When is it better to use List.PositionOf vs. a Merge?
I think it comes down to the data sources and the number of records. You should always perform some timing/performance tests using both methods to see which performs better or worse. One might be better in Situation A, but fair worse in Situation B. Thanks for watching.
Agreed with most people here, very easy for the excel users here to understand and transition to power query.
This is an exact match, do we have alternative for non exact match? Thanks!
I have that topic on my list of upcoming videos. Soon. Thanks for watching.
I have a business case that I send the sheet to 4 persons, then the main sheet with me .
i do vlookup to get the values in a specific column from the 4 persons is there anyway to do a vlookup but in the power query from the 4 sheets?
@@ahmedshalaby9343 Without having seen the data, I would assume simple merges would be the key to solving your issues.
what if if need to lookup to two differente values? what would be the solution in that case? is there any & option and wildcard like in EXCEL?
thanks in advance!
Power Query does not natively support wildcards. However, below is a link to an article on mimicking the behavior as close as possible:
gorilla.bi/power-query/wildcards/
Thanks a lot, I was using && but with no luck
@@jacopo326 I could have sworn I replied to your comment, but it appears to be missing. Glad you got it working.
Hi Professor, I notice that if I use "List.PositionOf" and it doesn't find the value, it will return a (-1), so, therefore the lookup returns an error. I could add another step to replace the errors, but if you find a walkaround to avoid another step, will be helpful. Thanks in advance for your feedback.
Either replace the errors, or nest the "List.PositionOf" within an IF to check for the -1 occurrences. Thanks for watching.