Power Query - Exact Match Lookups (1 Goal; 3 Methods)

Подписаться 7 тыс.

Просмотров 1,6 тыс.

50% 1

Learn how to perform an exact match lookup in Power query using 3 different methods: merging queries, and two approaches to "Excel-like" lookups (i.e., VLookup and Index/Match).
File Download Link:
www.bcti.com//...
00:08 Mission Objective
00:59 Download Sample Files
01:53 Merging Queries to perform a "Super Lookup"
02:45 Performing a Lookup Operation
06:42 Integrating Data Typing
08:18 Develop Solutions in Pieces
08:31 Combining Steps to Expedite Processing

Опубликовано:

24 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 37

@shrikeshpattni3188 3 дня назад

Like seriously the way you teach is just amazing! It rare to find a diamond like you!! (seriously)

@bcti-bcti 2 дня назад

That is such a sweet and heartwarming thing for you to say. Thank you!

@chandrakanthagarwal1174 2 дня назад

Can't agree more.

@bcti-bcti День назад

@@chandrakanthagarwal1174 Thank you!

@JuanCVazquez-s9k 2 дня назад

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 RU-vid.

@bcti-bcti 2 дня назад

Awwww. That is so nice of you to say! Thank you so much.

@Acheiropoietos День назад

This is interesting because the position ID acts like a unique table ID. I definitely have lots of uses for this, thank you.

@bcti-bcti День назад

@@Acheiropoietos Yes, it does. Many uses are possible. Glad it is helpful to you.

@Luciano_mp 3 дня назад

Wow, all the forms you presented are very well explained, very clear! Thank you very much for your classes. Thanks a lot!

@bcti-bcti 2 дня назад

Happy to have been of service!

@IvanCortinas_ES 3 дня назад

Interesting way to solve it. Thank you very much for sharing!!!

@bcti-bcti 2 дня назад

You are welcome!

@jonathanhardy3149 3 дня назад

Thank you, this technique will simplify a query I recently wrote!

@bcti-bcti 3 дня назад

It's so nice to hear when a video has immediate benefit! Glad it helped.

@kkravch 3 дня назад

Thanks, Professor! As usual, great content.

@bcti-bcti 2 дня назад

Thank you, great student!

@chrism9037 3 дня назад

Another great video, thank you!

@bcti-bcti 3 дня назад

Glad you enjoyed it!

@SakthiVel-ye3mo 3 дня назад

Fantastic explanation...

@bcti-bcti 3 дня назад

Thank You!!!! We appreciate your support.

@silvo4880 2 дня назад

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.

@bcti-bcti 2 дня назад

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.

@RonDavidowicz 3 дня назад

Nice solution. Any idea if there is any performance advantage to either method?

@bcti-bcti 3 дня назад

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.

@kkravch 3 дня назад

@@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

@Bhavik_Khatri 3 дня назад

Very nice video.

@bcti-bcti 3 дня назад

Thank you very much!

@ChiMickE 2 дня назад

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!

@scotolivera8207 2 дня назад

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

@bcti-bcti 2 дня назад

Performance will likely change depending on the data sources involved. But it's cool to know, if nothing else.

@bcti-bcti 2 дня назад

Thank YOU for your support. Let us know what you discover.

@scotolivera8207 2 дня назад

@@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.

@bcti-bcti День назад

@@scotolivera8207 Wow! That's good to know. What were the data sources?

@shellycastille4991 День назад

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?

@bcti-bcti День назад

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.