You WON'T BELIEVE These 10 HIDDEN Features in Excel Power Query 🤯

Подписаться 2,6 млн

Просмотров 259 тыс.

50% 1

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Ready to explore hidden features in Excel's Power Query Editor? Whether you're a seasoned pro or a budding enthusiast, these tips and tricks will transform your Power Query experience!
🔍 What's Inside:
▪️ Power Query Editor Shortcut: Learn the Alt + F12 shortcut to instantly access Power Query Editor from any workbook.
▪️ Quick Access Toolbar Customization: Customize the Power Query Quick Access Toolbar to include your most-used features for quicker access.
▪️ M-Function Typing Tip: Avoid typing errors in M functions with a simple trick.
▪️ Zoom-In/Out Shortcut: Use Ctrl + Shift and the plus/minus keys to zoom in and out in the Power Query Editor.
▪️ Automatic Column Header Sorting: Learn how to sort column headers automatically for better data organization.
▪️ Import Entire File Content: Discover how to import all data from a file, including future sheets, using the folder icon.
▪️ Add Table Name to Quick Access Toolbar in Excel: Make table renaming easier by adding the table name box to Excel's QAT.
▪️ Copy & Paste Queries Between Files: Effortlessly copy queries from one Excel file to another.
▪️ Add Slicers to Tables: Enhance table interactivity by adding slicers for user-friendly filtering.
▪️ Adjust Refresh Settings in Excel: Set your Power Query results to refresh automatically at specific intervals.
In this video we'll unlock some hidden gems in your Excel Power Query Editor that you had this WHOLE time. They are simple but they will make your work life a lot easier. Some are also hidden tips when you work with Excel power query results - such as updating query refresh times, adding slicers to tables and also making it easier to change table names in Excel.
00:00 Hidden features your Excel Power Query you had this whole time
00:22 Power Query Editor Excel Shortcut
01:14 Quick Access Toolbar in Power Query
02:27 New Course: Automate with Power Query from Leila
04:25 Properly Enter M-Functions in Power Query Editor
05:32 Zoom-in and out Shortcut Keys in PQ Editor
06:05 Automatically Sort Column Headers
06:48 Import Entire File Content in Power Query
08:01 Add Table Name to QAT-Bar in Excel
09:10 Copy & Paste Power Queries to Different Excel Files
10:01 Add Slicers to Tables
11:09 Adjust Refresh Settings in Excel
11:45 Wrap Up
🌍 My Online Courses ► www.xelplus.com/courses/
Read the blog post here: www.xelplus.com/power-query-1...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel #powerquery

Опубликовано:

15 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 342

@LeilaGharani Год назад

If you'd like to check out our brand new Automate with Power Query Course, here's the link: www.xelplus.com/course/power-query-recipes/ Over 3,000 people already signed up and started working through our automation recipes. If you've signed up already, THANK YOU 🙏

@mohammadtawhidulislam4459 Год назад

Already started without seeing any preview. Because it’s the Leila who never frustrated me.

@pl4195 Год назад

what are the differences between this course and the PQ begin to pro course?

@user-do5qy6wx3g Год назад

Hi Leila, I have seen your videos and really enjoy them. Would like to learn more as my work involves analysing multiple excel sheets and use of pivot tables. My data comes in sheets with different headers as well as columns that have combinations of number and characters within the same column. I would like to automate and analyse these columns in number format. Which course should would you recommend that I attend: (1) Power pivot & Dax( beginner to pro) or (2) Power Query ? Thanks.

@higgs168 11 месяцев назад

@@pl4195 same question

@robmedinaXXI Год назад

The addition of table names to the Quick Access Toolbar is a brilliant time-saver, enhancing workflow efficiency. The tip about not typing the dot in M functions until necessary is also very practical. Moreover, the use of slicers for user-friendly table filtering is a great touch. These tips are truly valuable!

@LeilaGharani Год назад

Yay! Glad you like it

@MissPickles1980 11 месяцев назад

The table names QAT is a game-changer for me!

@enocharthur4322 Год назад

Quick access toolbar and slicer were my favorite. Awesome video Leila. Keep them coming!!!

@Quijanos1 Год назад

Thank you! Like always a clear explanation with an easy to follow. Example. As per your question, I thoroughly enjoyed how you displayed the slicer and made it look super easy. Have a great day but an even better weekend.

@alperkins66 Год назад

Love the "no-dots" with M code, love the zoom up/down using Control-Shift&+ - and lastly love adding table name to the Quick Access Toolbar!! Thanks LG!!

@JJ_TheGreat Год назад

Those tips are my 2 favorites of this video, too!

@duds_sn Год назад

The "never type the dot" is mind-blowing. I use PQ every day in excel and PBI and I'm chocked that it's that simple to avoid that behavior! Thanks Leila!

@ccroche Год назад

Thanks for those tips! I already knew a few of them, but I still love them all!

@ForeverMods 11 месяцев назад

Best teacher ever!! Been subbed for years. Thank you so much for sharing.

@mattsnow2376 Год назад

Hi Leila, thanks for this video. I took advantage pf the the video to sign for your course. I just started it and in chapter 1 (module 1) talked about addressing some challenges dealing with messy pdf's which was really helpful to me. Great courses. Clear and good examples. Thanks again.

@pasztom84 7 месяцев назад

As always, great doze of useful knowledge. Every single tip is top. Thank you Leila

@MuhammadBilal-official Год назад

Thank you Leila for sharing these PQ tips... All these tips must be helpful for PQ users' productivity..

@KhalidKhans55 8 месяцев назад

Wow! Leila, you are so genius! All these tools and tricks are to save time and work efficiently with less errors and great accuracy making you more proficient in front of your bosses.

@DingDongGrahamBell Год назад

Love all Ten (10) of them! Thank YOU as always for the tips!

@m.raedallulu4166 Год назад

No. 3 and No. 6 are life savers! and No. 10 is interesting one. Thank you so much.

@zengkenny5709 Год назад

🎉 Great and useful tips as always! Thank you so much. ❤

@eddywijaya2204 11 месяцев назад

Thanks for the tips Leila!

@adnanabbasi9482 Год назад

Another helpful video, bundle of thanks!

@user-ht1xy7ns8w 9 месяцев назад

Love auto refresh when open. Thank you for all your tips sharing. Very very useful.

@shielamontealegre8144 Год назад

Thanks Leila, very helpful tips. I really like Tip#6 and #10 and QAT. I tried also copying steps from one query to another query by copying the specific mcode of that step via Advance Editor 😊

@mkparker99 Год назад

Awesome! I love the ability to add to the QAT. I didn't know I could do that.

@DontCallMeScooterrr 6 месяцев назад

OMG. The zoom shortcut and the query copy & paste will make my life infinitely better. Thanks, Leila, you are the best!

@LeilaGharani 6 месяцев назад

Glad it was helpful!

@Metzanine Год назад

These tips are GOLD!

@quentinbricard Год назад

Thank you for this video!

@GuillermoPalchik Год назад

OMG! #3 was SO helpful! Thank you Leila! You rock!😎

@LeilaGharani Год назад

Glad it was helpful!

@jayeshaw5705 Год назад

Amazing tips. You are a true master!

@earthlingt8231 Год назад

Hi Leila, I love watching your videos, and have learned a great deal from you over the time. I work in consulting. Each day, I work on several projects and each with several tasks. I have been thinking of creating an integrated excel tool that would function as a time tracker for everyday (a timesheet); a to do list for everyday and a dashboard illustrating with a nice chart the various tasks I would have worked on in a given period (for example, budgeting, scheduling, communication, strategy, etc.). This tool would allow me to filter the chart results for any given time - it could be specific, weeks, or months. This could be used to show a summary of performance / work completed in a given period of time (for example, to a supervisor as part of performance review). I was thinking the graph would utilize the data from the time tracker, which would be filled in daily and perhaps archived into a separate sheet so that I have clean tracker for everyday (same would go for the to do list). Is there a cleaner and faster way to do this? It would be nice if you could kindly make a video on this. :) Many thanks in advance.

@asmrindia Год назад

Very Nice Mam Query Shortcut, Slicer and Copy & Paste are very useful features. Thanks 😊

@shankardeb4933 Год назад

Really amazing features. Thanks a lot.

@shirleymoreman6725 Год назад

I like #5 to sort column headers too. I wish we could have a feature that would allow us to select the sequence in this dropdown as well.

@mjbah Год назад

Hi Leila. As always your videos are unbelievably helpful and resourceful. Nearly all are favourites. But Tip #1, Tip #3 and Tip #6 are the most gems you have revealed on this video. But Tip #3 is the top dog. I can't count how many times I have to edit an M code (which I believe will also be applicable when creating custom columns where you use M) after pressing a tab. Thanks again.

@freezendog7 Год назад

Hi Leila, #7 to keep an eye on things / quickly update table names. I'll start and use this quite a bit I think. Cheers. Pierre.

@soloseven007 Год назад

Love you! Thank you!

@pgcsec Год назад

3, 7 and 10 - great to get to learn new ways to work with Power Query - thank you.

@abdelazizallam Год назад

Hi Leila I learn so much from you and I love you simple way in teaching .. I like in this video trick of refresh setting that can make me auto refresh every specific time ..thanks

@mogarrett3045 Год назад

this is awesome...thank you

@rchsuccesspartnersllc155 11 месяцев назад

Awesome- Thank you so much

@michaeldavenport9268 11 месяцев назад

Awesome 3 & 9 are my favorites! love these

@SystemsDeptKIOCL 11 месяцев назад

Tip no 5 is my favorite your tutorials are brilliant and they have helped me a lot in understanding the brilliant tools in excel. Thank you

@englishwithabdullahsafi5034 5 месяцев назад

Thank you so much Leila!

@sergegagne874 Год назад

Good stuff, thank you! :)

@engelberthvelasquez4222 6 месяцев назад

Brutal...... An amazing Video

@fawdfire Год назад

Great Lesson Video

@piggybankaccountant Год назад

I really love the Quick Access Toolbar addon, even "outside" of power query, you cant "ALT + 1 (or 2 or 3)" any keyboard shortcut. Its so great. Good video!

@mostafaattia2476 7 месяцев назад

thank you so much Leila

@biancawilkinson8757 Год назад

Table Names in the QAT literally just blew my mind. So good. Thank you!

@nazarenoman 11 месяцев назад

Hola. Son todos muy brillantes. Gracias.

@Faith4AllofLife Год назад

Number 3!!! Yes, thank you!!!

@mhmdseadawy8228 8 месяцев назад

Thanks for this great video.

@chrism9037 Год назад

Thanks Leila! PQ is so amazing. I like #3 too

@LeilaGharani Год назад

It really is. Thanks Chris!

@farooqaulakh5482 Год назад

Dear Ms. Your lessons are really great. I have learnt so much from these lessons. I will be connected with you for learn more.

@jrioublanc Год назад

Thanks, #6 is really cool.

@Bourouwe Год назад

Tip #8 is my favorite; thank you for all the tips!

@leiterservices Год назад

Thanks for the "Refresh when opening file" tip!!!

@silverfunnel6819 Год назад

So much new information👍❤

@RogerStocker Год назад

Tip 8 I knew already and use it almost daily. for the rest I'm completely with you 3 & 6 are the most exiting ones.

@younesbell7884 10 месяцев назад

🤠grazie mille. very helpful

@meenatrainer 8 месяцев назад

Great video as always, all the tips are very informative 🙂

@markandrow4010 Год назад

Thank you Leila , one of your best tutorial.

@LeilaGharani Год назад

Glad you think so!

@andreashermle2716 8 месяцев назад

Leila, you are fantastic ... always coming up with things I haven't heard of yet

@LeilaGharani 8 месяцев назад

Thank you so much!

@Henrik.Vestergaard Год назад

1 and 6, nice, thanks. Used the rest allready.

@virendrahardiya2797 Год назад

Yes I loved these all skills you shown 🙏💓💓👍

@elphau Год назад

the best video i think i have to come replaying it everyday

@rpopecpa Год назад

Hi Leila, Great video. I love tips #2, 3, 7 & 8!!

@LeilaGharani Год назад

Thanks so much!

@vamp9750 Год назад

they are all new to me thanks

@jeffreydheere4737 5 месяцев назад

I liked the Slicer function. I didn't even know about Power Query a couple days ago, now I'm assigned to learn all about it and how to use it in gathering our testing results.

@thiagarajanneelamegam8640 8 месяцев назад

Always Awesome your teaching way. These 10 Shortcuts are very useful. Thanks Leila G

@LeilaGharani 8 месяцев назад

You're very welcome!

@igreen4460 Месяц назад

Thank you. All awesome tips! 😊

@LeilaGharani Месяц назад

Thanks for watching!

@user-rd6db1wy3b 9 месяцев назад

Love the tips. Have to practice it now. A bit fast though.

@flourishwithMJ 9 месяцев назад

Thank you.

@jayashribordoloi305 11 месяцев назад

Thank you for sharing useful tips. My favorite tip is 8. This will make my life easy

@hemvaidya Год назад

Thanks so much

@jollyjuln1054 Год назад

Always perfect🎉

@naorshikyar7648 Год назад

Incredible! I think that copy queries from workbooks to others is so helpful, thank u🙏

@LeilaGharani Год назад

You're so welcome!

@derekcapitaine8854 Год назад

#9 Slicers makes for a user-friendly table. Great tip. Thx.

@tafmarasha 9 месяцев назад

Copy/Paste Query just changed my life! Thank you! 🙏🏾🙏🏾

@iankr Год назад

Many thanks, Leila. I wish you could resize the table name field to make it longer!

@arthurlau-wx1nj 6 месяцев назад

Thank you so much

@chatureshpatel1159 8 месяцев назад

Excellent as usual ....

@karhukivi Год назад

Thank you Leila! I still struggle with graphing in Excel, especially the x-axis settings. I'll have to focus on that again!

@regihamp0206 Год назад

Wow 3 to 7 OMG - thanks!

@dimasagil90 Год назад

Thanks Leila. QAT is very practical. I will use it

@LeilaGharani Год назад

Glad it was helpful!

@rohitkarnani0506 Год назад

Tip 8 is savior, thank you so much:)

@imatzav Год назад

#8: You can even paste into a text editor. What is pasted is the M code.

@shirleymoreman6725 Год назад

I do this all the time!

@LeilaGharani Год назад

Thanks for the tip

@konstantinevseyenko9312 Год назад

I usually open two files in separate Excel instances and have to PQ editors. This allows me to copy queries between them more comfortable.

@CassidyHood 4 месяца назад

These are going to save me so much time! Thank you!!

@LeilaGharani 4 месяца назад

You're so welcome!

@roywilson9580 Год назад

Cool video, the only one that was new to me was the alt F12 to bring up the Power Query Editor, you can also use the right click key and 'G' to do the same thing.

@VahabSahvi 3 месяца назад

Excellent ❤

@jazzista1967 Год назад

Nice tricks. I knew a handful of those . on your tip # 3 ( It could be 3.1) if you type the first letters of the function for example Excel.Workbook you simpy type in EW , the function will appear. Same with Table.PromoteHeader you type TPH and the function shows in the formula Bar I use this a lot and it is a time saver. Regards

@prateekgupta7053 Год назад

Thank you mam😊😊. I liked every tip. ❤❤

@RichardJones73 Год назад

There I was only the other day wishing for a quicker way to start a new query from the code editor so that I can paste my template code. Thanks Leila 👍👍👍

@pradyumnmishra2370 Год назад

hi Leila thanks and great to see these ones, my favourate is number 3

@andrzejb4370 Год назад

No 8❤. Kind of easy but I haven't thought about it and certainly will be useful

@anjelarieder9924 6 месяцев назад

to be honest, I will use all of your tips! just made me a summary of all! thank you!

@LeilaGharani 6 месяцев назад

Perfect!

@bijaytanty8434 6 месяцев назад

Thanks

@kellyandrews9462 Год назад

Thanks!

@LeilaGharani Год назад

Thanks!

@jerrydellasala7643 Год назад

Leila, great video as always! I'm on the Insider Beta version of 365. A month or two ago an update changed how the name of the table is made adding "Table_" to the beginning of the table name I already gave the Query with the intention of using it as the Table Name. Your tip about adding it to the QAT is great to help resolve that problem! I've sent feedback asking to either stop that behavior or add it as an option and maybe even allow the prefix to be able to be customized. I will give tables a SHORT prefix sometimes like when using table data in a Query to be able to easily distinguish Tables from Query Tables by prefixing Query tables with "tbl", but that's vastly different than imposing "Table_" on EVERY Query Table! Have you seen this "feature" yet? Do you like it? Thanks as always for a great video!

@shirleymoreman6725 Год назад

This "feature" now seems to be on general release and I agree - it's really annoying! Like you, if I want a prefix I usually add tbl rather than Table_ so I end up renaming every time. Leila's tip to add the table rename to the QAT is a great idea... but I really wish that this could be switched off!

@chh8860 Год назад

I still aspire to someday soar with eagles like Leila, ... but alas, today I am only a parakeet with great ambitions ... 😥 Favorite tips #9 & #10. Thank you ... thank you ... thank you ... 😍😍😍 PS Hot coffee with a slice of pound cake and a Leila video ... a wonderful way to start my Thursday.

@LeilaGharani Год назад

Someone told me that ambition is the path to success. So, you're on the right track

@RichardJones73 11 месяцев назад

typing the dot caused me trigger issues so thank you Leila for curing me!

@filip2585 Год назад

this copy querys is awesome :D I always do copy into notepad and than copy to new blank query

@user-ue4oe9dy3e 3 месяца назад

really like your 3th tips. Im still learning M so having an example for any function is awesome

@LeilaGharani 3 месяца назад

Great to hear!

@JJ-ml7wo Год назад

Thank you! Love your videos. Have you ever looked up a product ID from a separate table set that lists multiple (alternate part numbers in one cell) to get the price of one trump item number, with / , and random madness? I tried a partial lookup but it’s not working . Any advice?