Be among the first 100 to jump on board "The Query Editor" newsletter 🚀. It's packed with super-actionable tips and insights. Why should you be excited? 🌟 Because the first 100 subscribers will receive an exclusive 50% discount on my upcoming mini-course, set to drop early next year (or perhaps even sooner)! 🤞Just hit this link to subscribe thequeryeditor.beehiiv.com/subscribe 🚀🔥
I like the way the code was built up step-by-step, using different techniques, revealing nuggets of knowledge. This helped reinforce and build on what I have learned elsewhere. I am really starting to appreciate how useful PQ can be. Thank you!
Thank you very much for the excellent advice on PQ. The explanations are excellent and straightforward. The language is clear, without accent, has the right pace and is excellent to understand for people who do not speak English as their first language. I like the format!
This was just what I was looking for re lookup syntax, lots of searches just showed how to merge, not what I wanted, great bit of theory behind it all as well.
Thank you for a great post once again. Totally love these tips! Secret #1 is so much better than a traditional query merge left outer only to remove the merged column; I have not tested performance, but am assuming it will be more efficient. Secret #2 prior indexed record is brilliant!.
You are awesome and very much helped us with your informative videos. Those 80% are more than stupid who still thinking for sub your channel. Pls don't be sad a bit & keep sharing such videos . 🙂
Ah, that’s a good question. Er… no. Merge will show the best performance. You may have to expand afterwards, but you can easily nest both steps into one.
@@thebhaskarjoshi I have created my profile as of this morning due to your request :) So I think you may just be the first person to connect with me :): www.linkedin.com/in/missmicrosoft/
Hi Rick, hope this helps: 04:13 UpdatedCodes {0} 1. if you create the custom column in the UpdatedCodes table using “UpdatedCodes{0}” it returns the entire record, so Current and New 2. If you create the custom column in the SalesData table using “UpdatedCodes {0}, then only the record that’s in row 1 of your UpdatedCodes table will be returned for the entire list of 5000 plus transactions in your SalesData table, so this is not dynamic 3. So in our SalesData table what we can do is perform a “VLOOKUP” so don’t use the {0} but in it’s place use [Current = Product ID]] and we get the record for the Current values returned only if Current equals Product ID, note this only returns the one field in our record, i.e. “Current” 4. But we need the values from the “New” column to be returned, so we need to first return the entire record from our UpdatedCodes table, which is “Current” and “New” and we do that by using our Positional Operators, i.e. the curly brackets as follows: “UpdatedCodes {[Current = Product ID]]}” 5. So the Positional Operators helps us return the entire record of our table and not just one field in the record, 6. Therefore when we used “UpdatedCodes{0} it returned the entire record of our UpdatedCodes table, so replace the {0} with {[Current = Product ID]]} and you get the entire record for the UpdatedCodes table again, but as mentioned in point 3 above we need to do the VLOOKUP first in the square brackets, then the positional operators helps us return the entire record where Current equals Product ID. 7. There is also an explanation at 01:34
Thank you Dennis, much appreciated! The Udemy course is Excel only and not Power Query, I’m actually going to remove it. I will be launching online group coaching sessions on Power Query and Power BI. If you would like to join the waitlist please email me at missmicrosoft@ignite-work.com
Thank you for this video 🙏🏽. I really appreciate the description of the position operator. A couple other surprising bracket notation that works it seems: [NestedTableColmn][Column1] works to pull out Column 1 from a nested table it seems 🤷🏻♀️. Also, please consider a video showing how the Refresh button at 14:09 was created. Also I like the VBA code presented in this video: ua-cam.com/video/2OqE-AXILz0/v-deo.html . The code has power query automatically refresh whenever a new selection is made with the drop down menu. Thank you 👌🏽.
The resolutions of your video is not 1080 - even if i choose it - it doesn't look like even 720p though.... Unfortunately. Not so pleasant for eyes. And for amount of likes and subscriptions) But Your lessons in PQ are good for me.
Be among the first 100 to jump on board "The Query Editor" newsletter 🚀. It's packed with super-actionable tips and insights. Why should you be excited? 🌟 Because the first 100 subscribers will receive an exclusive 50% discount on my upcoming mini-course, set to drop early next year (or perhaps even sooner)! 🤞Just hit this link to subscribe thequeryeditor.beehiiv.com/subscribe
🚀🔥
I like the way the code was built up step-by-step, using different techniques, revealing nuggets of knowledge. This helped reinforce and build on what I have learned elsewhere. I am really starting to appreciate how useful PQ can be. Thank you!
I'm so glad to hear that! Thank you :)
Thank you very much for the excellent advice on PQ. The explanations are excellent and straightforward. The language is clear, without accent, has the right pace and is excellent to understand for people who do not speak English as their first language. I like the format!
Thank you so much for your kind words, it means a lot! I'm really glad that you're enjoying the videos! Please keep watching :)
This was just what I was looking for re lookup syntax, lots of searches just showed how to merge, not what I wanted, great bit of theory behind it all as well.
Glad the video was helpful!
Thank you for a great post once again. Totally love these tips! Secret #1 is so much better than a traditional query merge left outer only to remove the merged column; I have not tested performance, but am assuming it will be more efficient. Secret #2 prior indexed record is brilliant!.
You are most welcome! Thank you for your support, I really appreciate it!
Very insightful and useful, especially when working in these kind of environments 👌
Glad it was helpful!
Your channel has been so helpful, please keep up the good work! Like and Subscribed and commented to help the algorithm!
Thank you so much Kaleb! I’m so glad the videos have been helpful and I really appreciate the support! 🙏
Great video! You got one more sub!!
Awesome, thank you!!!
You are awesome and very much helped us with your informative videos. Those 80% are more than stupid who still thinking for sub your channel. Pls don't be sad a bit & keep sharing such videos . 🙂
Thank you for your comment, I really appreciate it and thank you for the support 😊
I loved the second example and the vlookup one..do you see any performance improvement in using this mode of lookup vs using merge ?
Ah, that’s a good question. Er… no. Merge will show the best performance.
You may have to expand afterwards, but you can easily nest both steps into one.
Vidoe was built in a step-by-step manner, which I found to be very appealing.
Glad to hear you enjoyed the video!
@@MissMicrosoft How can I connect with you over LinkedIn?
@@thebhaskarjoshi I have created my profile as of this morning due to your request :) So I think you may just be the first person to connect with me :): www.linkedin.com/in/missmicrosoft/
@@MissMicrosoft I appreciate your initiative and welcome to the world of professionals there you will get to know many talented brains.
@@thebhaskarjoshi thank you! Please be patient with me as I build up my profile!
Excellent video as always 👍🌹
Thank you Kebin!
You jumped from {0} to {[Current=[Record ID]]}. How does the second one turn into a number?
Hi Rick, hope this helps:
04:13 UpdatedCodes {0}
1. if you create the custom column in the UpdatedCodes table using “UpdatedCodes{0}” it returns the entire record, so Current and New
2. If you create the custom column in the SalesData table using “UpdatedCodes {0}, then only the record that’s in row 1 of your UpdatedCodes table will be returned for the entire list of 5000 plus transactions in your SalesData table, so this is not dynamic
3. So in our SalesData table what we can do is perform a “VLOOKUP” so don’t use the {0} but in it’s place use [Current = Product ID]] and we get the record for the Current values returned only if Current equals Product ID, note this only returns the one field in our record, i.e. “Current”
4. But we need the values from the “New” column to be returned, so we need to first return the entire record from our UpdatedCodes table, which is “Current” and “New” and we do that by using our Positional Operators, i.e. the curly brackets as follows: “UpdatedCodes {[Current = Product ID]]}”
5. So the Positional Operators helps us return the entire record of our table and not just one field in the record,
6. Therefore when we used “UpdatedCodes{0} it returned the entire record of our UpdatedCodes table, so replace the {0} with {[Current = Product ID]]} and you get the entire record for the UpdatedCodes table again, but as mentioned in point 3 above we need to do the VLOOKUP first in the square brackets, then the positional operators helps us return the entire record where Current equals Product ID.
7. There is also an explanation at 01:34
@@MissMicrosoft Thank you this helps a lot!
Glad to hear Rick!
I love your videos, informative, valuable and ironic 😂 , i always love the Bi-engineers meeting part of all your videos😂😂😂😂
Thank you so much for watching! Glad you enjoy the Bi-engineers! They have a tough life 😂 😂
Nice video - well explained.
Much appreciated Martin :)
Thanks!
Thank you so much for your generosity, it means alot!!! 😊
Thanks for sharing ❤
You’re most welcome 😊
I really like your channel and how you explain things. Is your udemy course still available? When I click on the link it won't show up
Thank you Dennis, much appreciated! The Udemy course is Excel only and not Power Query, I’m actually going to remove it. I will be launching online group coaching sessions on Power Query and Power BI. If you would like to join the waitlist please email me at missmicrosoft@ignite-work.com
Thanks
You’re most welcome
Great!
Thank you!
Thank you for this video 🙏🏽. I really appreciate the description of the position operator. A couple other surprising bracket notation that works it seems: [NestedTableColmn][Column1] works to pull out Column 1 from a nested table it seems 🤷🏻♀️.
Also, please consider a video showing how the Refresh button at 14:09 was created. Also I like the VBA code presented in this video: ua-cam.com/video/2OqE-AXILz0/v-deo.html . The code has power query automatically refresh whenever a new selection is made with the drop down menu. Thank you 👌🏽.
You are most welcome!
The resolutions of your video is not 1080 - even if i choose it - it doesn't look like even 720p though.... Unfortunately. Not so pleasant for eyes. And for amount of likes and subscriptions)
But Your lessons in PQ are good for me.
Thank you for your comment, the rest of my viewers are able to watch the video without any issues, glad the lesson was helpful!
The first solution is a like a merge on steroid! Brilliant. 10:37
Glad you like it Jérome!
Thanks
Thank you so so much for your support, I highly appreciate it! I'm glad these videos are helpful!