Unlock 4 Power Query Secrets Now

Рет қаралды 8,993

Күн бұрын

What if I told you that it was possible to perform a bulk replacement on any data or to calculate the previous rows value, having no knowledge of M Code, and all you need to do is write one line of code. Well I’m going to show you just how to do that and a few more other Power Query Secrets. So be sure to watch until the end.
Please download the practice file here: docs.google.com/spreadsheets/...
00:00 Secret #1
07:52 Secret #2
11:47 Secret #3
14:14 Secret #4
✅ Please see link to videos mentioned in this video:
🎦 Power Query Advanced Editor - What Is It and How to Use It? - • Getting Started with t...
✅ If you would like to connect on LinkedIn:
🤝 www.linkedin.com/in/missmicrosoft
✅ If you would like to support the channel by buying me a coffee, (it's really quick and easy), please use this link:
☕www.buymeacoffee.com/missmicr...
✅ For more videos please subscribe:
🔔 / @missmicrosoft
✅ Please also check out my Excel course on Udemy, please use this link:
📖 www.udemy.com/course/excel-fo...

Пікірлер: 53

@MissMicrosoft 9 ай бұрын

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 🚀🔥

@dispirted8 Жыл бұрын

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!

@MissMicrosoft Жыл бұрын

I'm so glad to hear that! Thank you :)

@williamarthur4801 Жыл бұрын

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.

@MissMicrosoft Жыл бұрын

Glad the video was helpful!

@Helmut55 8 ай бұрын

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!

@MissMicrosoft 7 ай бұрын

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 :)

@YvesAustin 9 ай бұрын

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

@MissMicrosoft 9 ай бұрын

You are most welcome! Thank you for your support, I really appreciate it!

@alexrosen8762 Жыл бұрын

Very insightful and useful, especially when working in these kind of environments 👌

@MissMicrosoft Жыл бұрын

Glad it was helpful!

@YvesAustin 9 ай бұрын

Thanks!

@MissMicrosoft 9 ай бұрын

Thank you so much for your generosity, it means alot!!! 😊

@alterchannel2501 Жыл бұрын

I love your videos, informative, valuable and ironic 😂 , i always love the Bi-engineers meeting part of all your videos😂😂😂😂

@MissMicrosoft Жыл бұрын

Thank you so much for watching! Glad you enjoy the Bi-engineers! They have a tough life 😂 😂

@KalebBroadhead 6 ай бұрын

Your channel has been so helpful, please keep up the good work! Like and Subscribed and commented to help the algorithm!

@MissMicrosoft 6 ай бұрын

Thank you so much Kaleb! I’m so glad the videos have been helpful and I really appreciate the support! 🙏

@mirrrvelll5164 Жыл бұрын

Great video! You got one more sub!!

@MissMicrosoft Жыл бұрын

Awesome, thank you!!!

@martyc5674 Жыл бұрын

Nice video - well explained.

@MissMicrosoft Жыл бұрын

Much appreciated Martin :)

@kebincui Жыл бұрын

Excellent video as always 👍🌹

@MissMicrosoft Жыл бұрын

Thank you Kebin!

@FRANKWHITE1996 Жыл бұрын

Thanks for sharing ❤

@MissMicrosoft Жыл бұрын

You’re most welcome 😊

@MrDhunpagla Жыл бұрын

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

@MissMicrosoft Жыл бұрын

Thank you for your comment, I really appreciate it and thank you for the support 😊

@heishoi 4 ай бұрын

Thanks

@MissMicrosoft 4 ай бұрын

Thank you so so much for your support, I highly appreciate it! I'm glad these videos are helpful!

@djl8710 Жыл бұрын

Great!

@MissMicrosoft Жыл бұрын

Thank you!

@rick_. Жыл бұрын

You jumped from {0} to {[Current=[Record ID]]}. How does the second one turn into a number?

@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

@rick_. Жыл бұрын

@@MissMicrosoft Thank you this helps a lot!

@MissMicrosoft Жыл бұрын

Glad to hear Rick!

@Sumanth1601 Жыл бұрын

I loved the second example and the vlookup one..do you see any performance improvement in using this mode of lookup vs using merge ?

@GeertDelmulle Жыл бұрын

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 Жыл бұрын

Vidoe was built in a step-by-step manner, which I found to be very appealing.

@MissMicrosoft Жыл бұрын

Glad to hear you enjoyed the video!

@thebhaskarjoshi Жыл бұрын

@@MissMicrosoft How can I connect with you over LinkedIn?

@MissMicrosoft Жыл бұрын

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

@thebhaskarjoshi Жыл бұрын

@@MissMicrosoft I appreciate your initiative and welcome to the world of professionals there you will get to know many talented brains.

@MissMicrosoft Жыл бұрын

@@thebhaskarjoshi thank you! Please be patient with me as I build up my profile!

@dennism.5593 Жыл бұрын

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

@MissMicrosoft Жыл бұрын

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

@jeromeastier462 8 ай бұрын

The first solution is a like a merge on steroid! Brilliant. 10:37

@MissMicrosoft 8 ай бұрын

Glad you like it Jérome!

@malchicken Жыл бұрын

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: kzfaq.info/get/bejne/aLWheJByva-wqmQ.html . The code has power query automatically refresh whenever a new selection is made with the drop down menu. Thank you 👌🏽.

@MissMicrosoft 9 ай бұрын

You are most welcome!

@user-dr4od4tx7i Жыл бұрын

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.

@MissMicrosoft Жыл бұрын

Thank you for your comment, the rest of my viewers are able to watch the video without any issues, glad the lesson was helpful!