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 . 🙂
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
@@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/
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.
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
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 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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-2OqE-AXILz0.html . The code has power query automatically refresh whenever a new selection is made with the drop down menu. Thank you 👌🏽.
