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/
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?
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.
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
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
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
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.
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
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?
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!
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?
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?
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?
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.
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
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??
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
@@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??
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
Very underrated channel! Your content is great, you should position your content in a more TH-cam way like "stop doing this in excel, learn this in power query - forget vlookups!!"
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 thank you chandeep.... I am going through all your content this weekend 😀😀🙂🙂....your techniques are unique and can be life savers at many inatances
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.
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
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?
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
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 ?
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?
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
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.
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/
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?
That’s right! Merge is the only way to go for large tables.
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.
Thanks Vijay! I am glad that my videos are helpful.
FACTS!
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
Thanks for your nice words !
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?
I love your clear and concise TH-cam videos. Thank you
Glad you like them!
Hi Chandeep. Love tricks like this! So efficient and elegant. Thanks for sharing :)) Thumbs up!!
everytime I learn these small techniques I get exited to learn how to improve on it even more:) this helpes!
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
thank you so much
we can do that also without variables and without let-in
"Leaves[LeaveCount]{List.PositionOf(Leaves[EMP ID],[EMP ID])}"
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
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.
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
Did you solve it? I added a + 1, but it's taking me one position below
I understood the reason, there are values that are not in the reference table
Great technique, thanks a lot for the video it will be very helpful to all who r using power bi.
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?
Чим ваш метод кращий за звичайння злиття? (куча, ручної роботи, потрібно відраховувати нумерацію у списках)
Hi Chandeep, is it posisble to do it with multiple conditions as well?
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!
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.
@@GoodlyChandeepYep! That is what I am thinking. Won't be in BI, but Excel Power Pivot will work! 👍👍
Every video of you Making our life simple.... Thanks a lot buddy.
Appreciations for your great hardwork
Thank you so much 😀
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?
Hey, Can you explain on how to reverse first and last name with comma in power query from a certain column?
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?
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?
IMPORTANT its { not ( it took some time to see my error
Your course , mastering dax in power bi is going to live sessions or pre-recorded sessions? I would like to learn DAX from you
Live - goodly.co.in/powerbi-6day-workshop/
Pre-Recorded - goodly.co.in/learn-dax-powerbi/
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.
Although 2 lakh rows isn't much. It don't recommend this technique. Use relationships as much as possible.
Interesting... Thanks Chandeep
Glad you liked it!
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
It might be slow on large datasets.
Amazing
Very nice and simple technique😃
Thank you
really good. appreciated
Appreciate this thank a mil
Sir,
How to extract mulitiple columns data by using List.PositionOf function rather than using merge.
Please advise. Thanks
I'd recommend you to use Merge. I suspect that it is faster
@@GoodlyChandeep Thank you
Great stuff, really helpful
Ammazing approach
Thanks!
Good video. Very helpful 👍
GooooooooooD!!
How to get in contact with you
goodly.wordpress@gmail.com
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??
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
@@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??
Sir please reply
Sir, pls guide how can we get more than one record
use Left Outer Merge
Microsoft be like one problem 5 solutions
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
Very underrated channel! Your content is great, you should position your content in a more TH-cam way like "stop doing this in excel, learn this in power query - forget vlookups!!"
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.
I agree merges are faster.. I wouldn't recommend this on a large table
@@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
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
Hi, pls suggest if the calculation time with this method will also be lesser than Merge in power query?
Why make it so complicated?
Just use Merge queries - Left Outer then it work exact the same way with far better speed and accuracy.
Another great video! Thanks for sharing!!
what do when u have 2 milion rows, and each must look-up in 260 thousnad rows ;-)
It seems like Key Match Lookup could have been used as well:
Tablename{[Field Name = Condition]}[Column Name]
Another superb video. Thank you very much!
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.
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
3:45 I'm from the future...an "INDEX" DAX function for Power Query...nice!!
When am trying to combine the workbooks .. am getting the option table and sheet. If am selecting table it’s giving me error.
Awesome, that's super simple and very useful, thanks for taking time to guide us through this good trick!
Great video, Is this method faster than a merge?
nopes.. merges are a lot faster
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?
Yes just the cosmetics
Hi Chandeep, great as always, and helpful
8:27 Nice trick! Thanks.
Dude, love your videos. To the point and simple to understand. Subscribed!
Excellent 💯👍
Wow .. ❤
Nice tutorial Very useful! 😊👍
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
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 ?
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?
Try doing a fuzzy merge!
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
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.