Quick Vlookups in Power Query!

[@GoodlyChandeep]

Use this technique on smaller datasets which need enriching - like Dimension tables. Don't alternate this with Relationships between fact and dimension tables! Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

[@ratneshbansal5139]

Hi, I tried using this technique to link and pull data from 3 different queries. Step-1: Pull a variable from Query 2, this one is kind of helper column; Step-2: Pull the final value from Query 3, based on the variable in helper column from Query 2; But at the very first step it is showing identifier error… it is not taking the reference to Query 2! In the video you pulled data from a simple query from a table (dimension table); What is the limit of this technique?

[@maxsalfer]

That’s right! Merge is the only way to go for large tables.

[@vijaykrishnan4151]

I just love your videos, just straight to the point, no beating around. No grand entrance :). Thank you for such valuable power query tricks. I always have these questions in mind. You are answering them. I have found my love in power query again. Thanks, Chandeep. Keep it up.

[@GoodlyChandeep]

Thanks Vijay! I am glad that my videos are helpful.

[@skimpylemon8034]

FACTS!

[@jeromeastier462]

So elegant - i particularly appreciate your approach: you systematically go through the logic first, then show its various applications. I totally agree this channel is VERY underrated

[@GoodlyChandeep]

Thanks for your nice words !

[@PeterKontogeorgis]

Nice technique. It always seems a lot harder to do lookups than it should be. Any idea on the performance of this approach compared to a merge?

[@rikinzayituriki1414]

Hi Chandeep, is it posisble to do it with multiple conditions as well?

[@georgeww600]

I love your clear and concise KZbin videos. Thank you

[@GoodlyChandeep]

Glad you like them!

[@cbhang]

Hi, pls suggest if the calculation time with this method will also be lesser than Merge in power query?

[@Guilopes99]

Very underrated channel! Your content is great, you should position your content in a more KZbin way like "stop doing this in excel, learn this in power query - forget vlookups!!"

[@leroyholmes9131]

Your explanations are excellent. How would you do this if in one table both columns had nested tables and you wanted to add a column to one of the nested tables that pulled data from the other nested table?

[@qrsimon]

everytime I learn these small techniques I get exited to learn how to improve on it even more:) this helpes!

[@ziomekzedzielni1]

Would like to see the performance comparison of ur solutions comparing to what normal people uses. In this case that would be even more interesting because I noticed searching with lists is much slower rather than merging on bigger datasets. So even more steps with merging compensated rather than using lists. It's possible I use lists the wrong way so would like to see it on sets with couple thousands rows and 20+ columns

[@demris15]

Great video... very helpful... I would be interested to see a performance comparison with merges vs "vlookups" though ... I've been using this method to create Key columns and there is certainly a performance difference, but it is hard to tell exactly how much

[@kumshan1407]

Thanks very good technique to do vlookups in power query. Is it an optimized way of doing vlookups in power query? Will it work faster on larger dimension tables like more than 2 lakh rows of data.

[@GoodlyChandeep]

Although 2 lakh rows isn't much. It don't recommend this technique. Use relationships as much as possible.

[@wayneedmondson1065]

Hi Chandeep. Love tricks like this! So efficient and elegant. Thanks for sharing :)) Thumbs up!!

[@krishnakishorepeddisetti4387]

Bro... Amazing content.... i tried this ...but it is consuming more time than normal merge ..any thoughts ?? My request would be to create a dedicated playlist for m language.... It will help a lot of people

[@brandonm2099]

Also, a little over my head or I didn't get the initial references and had a fact table that had duplicates. Wasn't sure it that would work or if no duplicates allowed. Either way, the merge seemed faster to create although I'm assuming more latency probably.

[@georgeww600]

Hi Chandeep, I have to keep recreating the transformations because I can't figure out how to apply saved Advanced Editor M-Code to new data. My data is Sept 2020 and Sept 2021, Oct 2020 and Oct 2021, and Nov 2020 and Nove 2021. I want to keep the transformed data separate for each month. I'd like the pairs of months/years to start out in different folders. I keep having to recreate the action steps.

[@davidlopez-fe2lb]

what if our leaves table had 7 columns we wanted to bring over instead of 1 column, is there a way to pull all seven or would we need to do this for each column?

[@zdzislawkes]

Hi. I am wondering what are the differences between these approaches (e.g. does this affect the query performance?): let leavelist = Leaves [Leaves Count], position = List.PositionOf (Leaves [EMP ID], [EMP ID]) in leavelist {position} vs Leaves [Leaves Count] {List.PositionOf (Leaves [EMP ID], [EMP ID])} May be is it just cosmetics?

[@GoodlyChandeep]

Yes just the cosmetics

[@rohitkarnani0506]

Hi Chandeep, this was very helpful, thankyou, I have used the same in one of the automation process, it works well. But want to understand is there any limitation on this. The dataset i used has more than 1000 rows and lookup field is approx 150

[@GoodlyChandeep]

It might be slow on large datasets.

[@YvesAustin]

Thank you Chandeep; yet another use case of lists! Reading your comment here Chandeep: "use this on smaller data sets". Can I apply this to a table with 100,000rows? I have exactly this problem on a very large scale. The merge takes a long time. I am considering testing Table.Buffer or maybe better put it all in the Data Model and DAX it all out!

[@GoodlyChandeep]

Merges will be faster than this approach. Why can't you ditch the merge in PQ and instead work with relationships in PowerBI. They are super snappy.

[@YvesAustin]

@@GoodlyChandeepYep! That is what I am thinking. Won't be in BI, but Excel Power Pivot will work! 👍👍

[@UlyssesHaq]

Excellent knowledge, thank you for sharing. You say this is good for dimension tables, does this mean this would not be good for doing a lookup against fact tables, with say.. 6million rows... Performance issues maybe?

[@garysmith3777]

Great video, Is this method faster than a merge?

[@GoodlyChandeep]

nopes.. merges are a lot faster

[@prateekkalra3864]

Hey, Can you explain on how to reverse first and last name with comma in power query from a certain column?

[@yussufabdallah4826]

thank you so much we can do that also without variables and without let-in "Leaves[LeaveCount]{List.PositionOf(Leaves[EMP ID],[EMP ID])}"

[@aryansena7290]

Hi , Can you create a table as per slicer selection .. consider I'd column in a rows like 1,2,3,4. And a slicer using this I'd column.. now if I select I'd from slicer 3 then a table will be created which has all data with I'd 3. Please let me know if requirement is not clear

[@srininvasrao7629]

Dear sir, your power query teaching and videos are done in a professional way, i all ways follow your video!!! I have a query !!! When I am handling with tables having above 5 lakhs when I have to compare the data tables with another master tables Excel is taking long time for retrieving and running query when I am using merged query!!! Can I make the query run faster by using list . Functions / mcode to overcome it , pls suggest to improve query running time ?

[@bagnon]

It seems like Key Match Lookup could have been used as well: Tablename{[Field Name = Condition]}[Column Name]

[@FRANKWHITE1996]

Another great video! Thanks for sharing!!

[@eysinesvolley-ball6955]

Awesome, that's super simple and very useful, thanks for taking time to guide us through this good trick!

[@marcusmayer1055]

Чим ваш метод кращий за звичайння злиття? (куча, ручної роботи, потрібно відраховувати нумерацію у списках)

[@syedaneesdurez7197]

Sir, How to extract mulitiple columns data by using List.PositionOf function rather than using merge. Please advise. Thanks

[@GoodlyChandeep]

I'd recommend you to use Merge. I suspect that it is faster

[@syedaneesdurez8766]

@@GoodlyChandeep Thank you

[@alexking8222]

Another superb video. Thank you very much!

[@M4D5K1L2]

Dude, love your videos. To the point and simple to understand. Subscribed!

[@rajkumargerard5474]

Great technique, thanks a lot for the video it will be very helpful to all who r using power bi.

[@krishnakishorepeddisetti4387]

Hi Chandeep, i tried this approach...but the data load with this is very slow compared to Merge and both merge and positionof make the file size same. how is this approach beneficial. help me with your inputs Thanks and Regards Krishna P.

[@GoodlyChandeep]

I agree merges are faster.. I wouldn't recommend this on a large table

[@krishnakishorepeddisetti4387]

@@GoodlyChandeep thank you chandeep.... I am going through all your content this weekend 😀😀🙂🙂....your techniques are unique and can be life savers at many inatances

[@RohitThakur-ku8sb]

Your course , mastering dax in power bi is going to live sessions or pre-recorded sessions? I would like to learn DAX from you

[@GoodlyChandeep]

Live - goodly.co.in/powerbi-6day-workshop/ Pre-Recorded - goodly.co.in/learn-dax-powerbi/

[@ShamalKaden]

I have a query on this such I have a large file of 9 companies where the inventory is handling in 2 systems. I had to pivot to make a summary of 9 companies and need to compare the same through Vlookup. Is there any easy way to do this?

[@rrrraaaacccc80]

Excellent 💯👍

[@williamarthur4801]

Really liked the List.Postionof which I've never used before, tying before watching, I used SelectRows, Table.AddColumn(Source, "Custom", each Table.SelectRows( IdCount, (A)=> A[Id] = _ [Id] ) [Id Count] {0} ), if you have a list of multiple matches rather than the positional place you can wrap in List.Sum.

[@elrevesyelderecho]

3:45 I'm from the future...an "INDEX" DAX function for Power Query...nice!!

[@abhinayrozer7379]

Every video of you Making our life simple.... Thanks a lot buddy. Appreciations for your great hardwork

[@GoodlyChandeep]

Thank you so much 😀

[@mahnoorafridi9872]

Very nice and simple technique😃

[@GoodlyChandeep]

Thank you

[@xLidyx]

I don't get it. I did exactly the same tables and exactly the same m code, still got Column1= null and Leave Count = error

[@madhun8092]

When am trying to combine the workbooks .. am getting the option table and sheet. If am selecting table it’s giving me error.

[@caioalabarse8933]

Ammazing approach

[@GoodlyChandeep]

Thanks!

[@gabrielgordon]

Hi Chandeep, great as always, and helpful

[@safrasism]

i got below error when i use this code. let valueneed= #"Master-Countries"[UID] , position=List.PositionOf(#"Master-Countries"[Country],[Country]) in valueneed{position} Error message below Expression.Error: The index cannot be negative. Details: Value=[List] Index=-1

[@clementefu3306]

Did you solve it? I added a + 1, but it's taking me one position below

[@clementefu3306]

I understood the reason, there are values that are not in the reference table

[@idakwiatkowska2862]

It's cool. But how do you proceed when the lists aren't exactly the same? Like in one list you have "name xyz" and in another list "the name Xy. Z" and you want to search for name?

[@GoodlyChandeep]

Try doing a fuzzy merge!

[@mattschoular8844]

Interesting... Thanks Chandeep

[@GoodlyChandeep]

Glad you liked it!

[@elrevesyelderecho]

8:27 Nice trick! Thanks.

[@jonaskarlsson477]

IMPORTANT its { not ( it took some time to see my error

[@hiteshjal143]

Amazing

[@LinhTran-ys1mt]

Why make it so complicated? Just use Merge queries - Left Outer then it work exact the same way with far better speed and accuracy.

[@PValili]

really good. appreciated

[@paolosoloperto4948]

Good video. Very helpful 👍

[@winbin82]

GooooooooooD!!

[@vijayarjunwadkar]

Nice tutorial Very useful! 😊👍

[@DineshP-v4y]

Wow .. ❤

[@morpando926]

Great stuff, really helpful

[@singhalmonica]

Sir, pls guide how can we get more than one record

[@GoodlyChandeep]

use Left Outer Merge

[@keagankemp6275]

Appreciate this thank a mil

[@mayurt4628]

How to get in contact with you

[@GoodlyChandeep]

[e-mail]

[@namangarg7875]

Suppose i purchase ur course and after taking the course if i encounter some doubts in your vdos how do they are resolved because that is the main thing if the doubt is solved withing shorter time frame??

[@GoodlyChandeep]

Hi Naman, I typically answer doubts within a day or two. But there are also times when I do not know the answer in which case it takes longer or worst I am not able to solve at all

[@namangarg7875]

@@GoodlyChandeep if a person like you says like that i am not able to answer makes me doubtful for this DAX complications. Because the way u have explained anything in your vdos is phenomenal thanks for context transition. Sir will i be getting the practice files in your course of DAX?? And how to contact you or your team for further enquiry about your course??

[@namangarg7875]

Sir please reply

[@pk5134]

Great video! Could you please do a video on functions ? Using let, in and each and _ operators ? I find it confusing accessing elements of nested tables. How would you go about doing that

[@kriskris4760]

what do when u have 2 milion rows, and each must look-up in 260 thousnad rows ;-)

[@riddharupbhattacharyya2963]

Microsoft be like one problem 5 solutions

[@ashishmohan4707]

Wow chandeep Very informative tutorial Your way of explanation is very clear I have 1 request can you plzzcheck ur mail I have sent u something Please can you reply there. Regards, AM