You WON'T BELIEVE These 10 HIDDEN Features in Excel Power Query 🤯
Рет қаралды 253,453
Күн бұрын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
@LeilaGharani 11 ай бұрын
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 11 ай бұрын
Already started without seeing any preview. Because it’s the Leila who never frustrated me.
@pl4195 11 ай бұрын
what are the differences between this course and the PQ begin to pro course?
@user-do5qy6wx3g 11 ай бұрын
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 11 ай бұрын
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 11 ай бұрын
Yay! Glad you like it
@MissPickles1980 10 ай бұрын
The table names QAT is a game-changer for me!
@enocharthur4322 11 ай бұрын
Quick access toolbar and slicer were my favorite. Awesome video Leila. Keep them coming!!!
@ccroche 11 ай бұрын
Thanks for those tips! I already knew a few of them, but I still love them all!
@DingDongGrahamBell 11 ай бұрын
Love all Ten (10) of them! Thank YOU as always for the tips!
@GeertDelmulle 11 ай бұрын
Some remarks: - I like that sorting columns trick and that implicit GoTo column trick as well. - Ha! I use the Table Name on the Excel QAT for the longest time, now. So, yes, great trick. - My current favorite is: the New Blank Query button on the Excel QAT (yes, exactly that). - if you first add a “(“ in your formula, you can use the dot w/o duplication issues. That’s how I do it. The intellisense is actually quite smart. I tend to wrap my argument and then write the function name, so the dot is no problem for me.
@LeilaGharani 11 ай бұрын
Always a pleasure reading your comments, Geert! Thanks for watching.
@shirleymoreman6725 11 ай бұрын
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.
@ForeverMods 11 ай бұрын
Best teacher ever!! Been subbed for years. Thank you so much for sharing.
@mkparker99 11 ай бұрын
Awesome! I love the ability to add to the QAT. I didn't know I could do that.
@Quijanos1 11 ай бұрын
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.
@zengkenny5709 11 ай бұрын
🎉 Great and useful tips as always! Thank you so much. ❤
@shielamontealegre8144 11 ай бұрын
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 😊
@user-ht1xy7ns8w 8 ай бұрын
Love auto refresh when open. Thank you for all your tips sharing. Very very useful.
@asmrindia 11 ай бұрын
Very Nice Mam Query Shortcut, Slicer and Copy & Paste are very useful features. Thanks 😊
@duds_sn 11 ай бұрын
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!
@chh8860 11 ай бұрын
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 11 ай бұрын
Someone told me that ambition is the path to success. So, you're on the right track
@pasztom84 7 ай бұрын
As always, great doze of useful knowledge. Every single tip is top. Thank you Leila
@MuhammadBilal-official 11 ай бұрын
Thank you Leila for sharing these PQ tips... All these tips must be helpful for PQ users' productivity..
@eddywijaya1924 10 ай бұрын
Thanks for the tips Leila!
@pgcsec 11 ай бұрын
3, 7 and 10 - great to get to learn new ways to work with Power Query - thank you.
@haydenn8210 11 ай бұрын
After extensive use of Power Query as an analyst in an SME that hasn't quite grown to needing a CRM and has disaggregated data, my 2 biggest efficiencies additional to what you've mentioned in this video have been: 1) Disabling "Enable background refresh" under query properties. When you have either many small or several large resource intense queries that you can't push further upstream (into SQL for example), this will force excel to execute the code in a single query at a time, drastically increasing refresh speed in my experience. It is also necessary to deselect this option if you want a MsgBox in VBA to appear AFTER the refresh has executed (really great feature for non savvy end users IMO). You'll also need to do this if you ever want to wrap a query refresh in a timer function in VBA to measure how long your queries take to refresh, which is great for the developer side of things. 2) Disabling M intellisense. Under Query Options > Global > Power Query Editor > Formula, there is the option to enable/disable M Intellisense. Having this enabled can be very handy when you're new to M code, but if you've been using it for a while, your projects are becoming larger and you find yourself manually editing the M code, this was an incredible timesave for me. Intellisense requires an insane amount of resources, so disabling it turned manually editing M code from borderline undoable to near instantaneous for me. Special mention: Ensure that you have fast data load enabled on BOTH individual queries AND under the Global > Data Load option. Global > Data Load will supersede individual queries, so if it's not enabled, your queries will still load slowly even if they individually have fast data load enabled.
@LeilaGharani 11 ай бұрын
These are great tips! Many thanks for sharing.
@freezendog7 11 ай бұрын
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.
@imatzav 11 ай бұрын
#8: You can even paste into a text editor. What is pasted is the M code.
@shirleymoreman6725 11 ай бұрын
I do this all the time!
@LeilaGharani 11 ай бұрын
Thanks for the tip
@konstantinevseyenko9312 11 ай бұрын
I usually open two files in separate Excel instances and have to PQ editors. This allows me to copy queries between them more comfortable.
@mjbah 11 ай бұрын
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.
@m.raedallulu4166 11 ай бұрын
No. 3 and No. 6 are life savers! and No. 10 is interesting one. Thank you so much.
@mattsnow2376 11 ай бұрын
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.
@alperkins66 11 ай бұрын
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 11 ай бұрын
Those tips are my 2 favorites of this video, too!
@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!
@adnanabbasi9482 11 ай бұрын
Another helpful video, bundle of thanks!
@piggybankaccountant 11 ай бұрын
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!
@earthlingt8231 11 ай бұрын
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.
@michaeldavenport9268 10 ай бұрын
Awesome 3 & 9 are my favorites! love these
@RogerStocker 11 ай бұрын
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.
@abdelazizallam 11 ай бұрын
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
@englishwithabdullahsafi5034 4 ай бұрын
Thank you so much Leila!
@Metzanine 11 ай бұрын
These tips are GOLD!
@shankardeb4933 11 ай бұрын
Really amazing features. Thanks a lot.
@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.
@GuillermoPalchik 11 ай бұрын
OMG! #3 was SO helpful! Thank you Leila! You rock!😎
@LeilaGharani 11 ай бұрын
Glad it was helpful!
@SystemsDeptKIOCL 10 ай бұрын
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
@jayashribordoloi305 10 ай бұрын
Thank you for sharing useful tips. My favorite tip is 8. This will make my life easy
@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.
@karhukivi 11 ай бұрын
Thank you Leila! I still struggle with graphing in Excel, especially the x-axis settings. I'll have to focus on that again!
@chrism9037 11 ай бұрын
Thanks Leila! PQ is so amazing. I like #3 too
@LeilaGharani 11 ай бұрын
It really is. Thanks Chris!
@jayeshaw5705 11 ай бұрын
Amazing tips. You are a true master!
@nazarenoman 10 ай бұрын
Hola. Son todos muy brillantes. Gracias.
@Bourouwe 11 ай бұрын
Tip #8 is my favorite; thank you for all the tips!
@biancawilkinson8757 11 ай бұрын
Table Names in the QAT literally just blew my mind. So good. Thank you!
@quentinbricard 11 ай бұрын
Thank you for this video!
@farooqaulakh5482 11 ай бұрын
Dear Ms. Your lessons are really great. I have learnt so much from these lessons. I will be connected with you for learn more.
@soloseven007 11 ай бұрын
Love you! Thank you!
@meenatrainer 8 ай бұрын
Great video as always, all the tips are very informative 🙂
@rchsuccesspartnersllc155 10 ай бұрын
Awesome- Thank you so much
@roywilson9580 11 ай бұрын
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.
@TanteHela 7 ай бұрын
Another Leila "gem". My favorite is the do-not-dot (eye opener). Thank you, thank you, thank you. My personal favorite is to disable "Adjust Column width" as soon as PQ creates the table. Right mouse click in the table > Table > External Data Properties. As a result the columns-widths do not change every time the query refreshes.
@LeilaGharani 7 ай бұрын
Thanks for sharing!!
@user-rd6db1wy3b 9 ай бұрын
Love the tips. Have to practice it now. A bit fast though.
@mogarrett3045 11 ай бұрын
this is awesome...thank you
@virendrahardiya2797 11 ай бұрын
Yes I loved these all skills you shown 🙏💓💓👍
@Henrik.Vestergaard 11 ай бұрын
1 and 6, nice, thanks. Used the rest allready.
@dimasagil90 11 ай бұрын
Thanks Leila. QAT is very practical. I will use it
@LeilaGharani 11 ай бұрын
Glad it was helpful!
@leiterservices 11 ай бұрын
Thanks for the "Refresh when opening file" tip!!!
@naorshikyar7648 11 ай бұрын
Incredible! I think that copy queries from workbooks to others is so helpful, thank u🙏
@LeilaGharani 11 ай бұрын
You're so welcome!
@markandrow4010 11 ай бұрын
Thank you Leila , one of your best tutorial.
@LeilaGharani 11 ай бұрын
Glad you think so!
@rpopecpa 11 ай бұрын
Hi Leila, Great video. I love tips #2, 3, 7 & 8!!
@LeilaGharani 11 ай бұрын
Thanks so much!
@jrioublanc 11 ай бұрын
Thanks, #6 is really cool.
@derekcapitaine8854 11 ай бұрын
#9 Slicers makes for a user-friendly table. Great tip. Thx.
@iankr 11 ай бұрын
Many thanks, Leila. I wish you could resize the table name field to make it longer!
@andreashermle2716 7 ай бұрын
Leila, you are fantastic ... always coming up with things I haven't heard of yet
@LeilaGharani 7 ай бұрын
Thank you so much!
@FreakAzoiyd 9 ай бұрын
I used power query and Excel to create a dynamic PV tool to calculate PV energy yield with the help of PVGIS Web API, compare individuel yield years to electricity consumption to simulate own consumption rates, optional battery simulation, dynamicly chart it, generate presentation ready tables and graphics. Features 1 to 8 PV configurations in one calculation (like for different roofs). Just with new Excel features like spill and Power Query. It's super stable and in use since 2 years. What's really cool but quite complicated is wrapping code into functions. 🥰 Love how clean PowerQuery code can be produced, while most people's UI created code is a mess that breaks on minor disturbances 😅
@jazzista1967 11 ай бұрын
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
@fawdfire 11 ай бұрын
Great Lesson Video
@thiagarajanneelamegam8640 7 ай бұрын
Always Awesome your teaching way. These 10 Shortcuts are very useful. Thanks Leila G
@LeilaGharani 7 ай бұрын
You're very welcome!
@pradyumnmishra2370 11 ай бұрын
hi Leila thanks and great to see these ones, my favourate is number 3
@mostafaattia2476 7 ай бұрын
thank you so much Leila
@RichardJones73 11 ай бұрын
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 👍👍👍
@engelberthvelasquez4222 5 ай бұрын
Brutal...... An amazing Video
@mhmdseadawy8228 8 ай бұрын
Thanks for this great video.
@sergegagne874 11 ай бұрын
Good stuff, thank you! :)
@Faith4AllofLife 11 ай бұрын
Number 3!!! Yes, thank you!!!
@igreen4460 Ай бұрын
Thank you. All awesome tips! 😊
@LeilaGharani Ай бұрын
Thanks for watching!
@dziadeck47 11 ай бұрын
So many good ones No.4 is a life saver when on multi-screen setup excel decides making tables xxxl is the way to go (really excel?) No 7 makes the housekeeping easier which is cool too
@davidunwin7868 10 ай бұрын
I think your tip #6 just solved one problem I've been struggling with. I have one monthly data dump file that has a sheet for each date range of payroll data, so about 6-8 sheets. All the tables are the same format, but combining them all into a single table is a multi step process, creating connections to each table and then appending. But I think with tip#6 I can do it all in just a few clicks! 🤯
@chatureshpatel1159 8 ай бұрын
Excellent as usual ....
@younesbell7884 10 ай бұрын
🤠grazie mille. very helpful
@elphau 11 ай бұрын
the best video i think i have to come replaying it everyday
@andrzejb4370 11 ай бұрын
No 8❤. Kind of easy but I haven't thought about it and certainly will be useful
@tafmarasha 8 ай бұрын
Copy/Paste Query just changed my life! Thank you! 🙏🏾🙏🏾
@giuseppedonato5677 11 ай бұрын
😮 with #3 you changed my day 😂 thanks. Have a good day
@user-hg4rg6jf9k 10 ай бұрын
С удовольствием смотрю ваши ролики
@silverfunnel6819 11 ай бұрын
So much new information👍❤
@fano974 11 ай бұрын
Impressive! I've been using PQ for a while and I'd never heard of the shortcut alt+f12 : game changer for me :-)
@anjelarieder9924 6 ай бұрын
to be honest, I will use all of your tips! just made me a summary of all! thank you!
@LeilaGharani 6 ай бұрын
Perfect!
@HarshilPatel497 11 ай бұрын
I usually do not like and comment on videos from years just keep on watching but..... certainly one of the best video i watched ever that deserve a like and a comment...
@LeilaGharani 11 ай бұрын
That's great to hear, thanks!
@Utubemop 11 ай бұрын
HI, my favourite was #9, not for the tip to add a slicer but for the fact that you can use the columns feature to bring the entries into one row...something I was looking for just earlier this week and could not resolve LOL 😂Thank you!
@JJ_TheGreat 11 ай бұрын
…But that’s not really at Power Query tip. That is more of a general Excel tip.
@Utubemop 11 ай бұрын
@@JJ_TheGreat ..yes...but still it was my favourite part when watching.
@MarcosAGazovic 2 ай бұрын
These features will have a big impact on my daily work. Thanks a lot. 🤓
@LeilaGharani 2 ай бұрын
Our pleasure!
@rohitkarnani0506 11 ай бұрын
Tip 8 is savior, thank you so much:)
@vamp9750 11 ай бұрын
they are all new to me thanks
@CassidyHood 3 ай бұрын
These are going to save me so much time! Thank you!!
@LeilaGharani 3 ай бұрын
You're so welcome!
@Soulenergy31 7 ай бұрын
3:13 A good doctor does not come out of medical school but he/she come out from different clinics and hospitalization....... Loved this quote Leila!!!!!!!!!!!!!!!!!! Thanks for the inspiration
@canirmalchoudhary8173 11 ай бұрын
I like the "selecting folder" and "PQ launch shortcut"