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

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

🚩Let’s connect on social:

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]

Over 3, 000 people already signed up and started working through our automation recipes.
If you've signed up already, THANK YOU 🙏

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

[alperkins]

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

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

[DontCallMeScooterrr]

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

[shirleymoreman]

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.

[haydenn]

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.

[m.raedallulu]

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

[KhalidKhans]

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.

[jazzista]

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

[imatzav]

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

[jeffreydheere]

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.

[GeertDelmulle]

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.

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

[Manoo_Addu_Vlogs]

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

[biancawilkinson]

Table Names in the QAT literally just blew my mind. So good. 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

[pasztom]

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

[pgcsec]

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

[asmrindia]

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