Power Query Editor Tips & Tricks in Excel

Unlock the hidden gems of Excel’s Power Query Editor with these clever tips and hacks—even pros may have missed a few! These tricks are simple yet will make your workflow much easier.

---

Tips & Tricks

1. Power Query Editor Shortcut

• Use Alt + F12 to open the Power Query Editor from anywhere in your workbook.
• If you’re on a query result table, Alt + F12 goes directly to that specific query.
• If no queries exist, this shortcut opens a blank Power Query Editor for you to start importing files.

2. Power Query Quick Access Toolbar (QAT)

• Add frequently used features (e.g., “Column Quality”, “Go To Column”, “Blank Query”) to the QAT by right-clicking and selecting "Add to Quick Access Toolbar".
• You can show the QAT below the ribbon if it gets crowded.
• This helps minimize navigation within multiple tabs.

3. Don’t Type the Dot When Writing M Functions

• When typing M code, don’t type the dot (.) when using autocomplete for functions.
• Instead, keep typing until you see the desired function, then hit Tab to accept.
• For function help, press Enter after typing the function name to see help info inside the editor.

4. Zooming in Power Query Editor

• Unlike Excel, use Ctrl + Shift + + (plus) to zoom in, and Ctrl + Shift + - (minus) to zoom out within the Power Query Editor.

5. Automatically Sort Column Headers

• To alphabetically sort column headers:
  • Go to "Choose Columns".
  • Adjust the order by selecting "Name" for alphabetical ordering.
  • Click OK to apply—useful for large datasets.

6. Select Folder Icon to Grab Everything

• When importing from a file with multiple sheets or sections, select the folder icon (not "Select Multiple Items") to automatically import all data and dynamically include future additions.
• Works on PDFs as well as Excel files.

7. Add Table Name Box to QAT

• You can easily rename tables by adding the Table Name field to the QAT in Excel:
  • Go to "Table Design" → "Table Name", right-click and add to QAT.
  • Rearrange QAT items for convenience.

8. Copy and Paste Queries Between Files

• You can right-click and copy a query (including dependent queries) from one workbook and paste into another via "Data" → "Queries and Connections".

9. Add Slicers to Tables for Easier Filtering

• Add slicers for table results:
  • Click within the table, go to "Table Design", then "Insert Slicer".
  • Choose a column to filter; arrange and customize slicers for easy one-click filtering.

10. Adjust Refresh Settings

• Set Power Query results to refresh automatically:
  • Go to "Data" → "Refresh All" → "Connection Properties".
  • Choose to refresh every X minutes and/or on file open for always up-to-date data.

---

Bonus: Power Query Automation Course

• New course available: "Automate with Power Query: Recipes to Solve Business Data Challenges".
  • Designed for business users to automate messy data tasks confidently.
  • Includes other automation tools (Power Automate, VBA, Office Scripts, ChatGPT).
  • Teaches broader automation possibilities for business processes.
  • [Course link provided in description]

---

Closing

• Try out these Power Query tips and tricks in your workflow.
• Share your favorite tip in the comments.
• Check out the Automation Course if you want to deepen your Power Query and broader automation skills.

---

Thank you for watching!