#1 – Power Query Editor Launcher

This one is very simple, and only saves a few clicks.  But if you open the Power Query editor over and over again, saving a few clicks here and there over time ends up amounting to a faster workflow.

To open the Power Query Editor, press the ALT-F12 keys combination on the keyboard.

Keyboard shortcut to launch Power Query (Alt + F12)

If you happen to have your cursor sitting in the output of a query, ALT-F12 will open the Power Query Editor and place you on the last step of the query that produced that output.

#2 – Power Query Quick Access Toolbar Customization

Power Query has a customizable Quick Access Toolbar (or QAT for short) just like regular Excel.

Fragment of Power Query Editor window showing the Quick Access Toolbar on top of the ribbon

It’s not as feature-rich as the QAT in Excel, but it can be a great way to consolidate your most used Power Query actions into a single one-stop shop.

To add a feature to the Power Query QAT, right-click a feature on a ribbon and choose “Add to Quick Access Toolbar”.

Fragment of Power Query Editor ribbon with context menu showing "Add to Quick Access Toolbar"

Over time, you will have curated the perfect place to access your most saved features.

Fragment of Power Query Editor window showing the Quick Access Toolbar on top of the ribbon

You can remove a feature from the QAT by right-clicking an icon and selecting “Remove from Quick Access Toolbar”.

You can also move the QAT below the ribbon to provide more room if you are inclined to add more buttons that the Toolbar would care to support.  To do this, right-click any button on the QAT and select “Show Quick Access Toolbar Below the Ribbon”.

Fragment of Power Query Editor ribbon with context menu showing "Show Quick Access Toolbar below the Ribbon"
Fragment of Power Query Editor window showing the Quick Access Toolbar below the ribbon

Featured Course

Automate With Power Query – Recipes to solve business data challenges

Do you want to become more confident using Power Query and automate your entire data workflow? Join this course and learn from real-world scenarios.
Learn More

#3 – Avoid IntelliSense Errors by Not Typing the Dot

When writing formulas using Power Query functions, we are presented with the same formula writing assistant known as IntelliSense.  This is the service that offers suggested function names as well as helpful insights into the needs of a function.

Custom Column window in the Power Query Editor showing beginning of a formula and the Intellisense context menu expanded.

Power Query’s IntelliSense service is going through a bit of an awkward teenage phase where it thinks it knows what to do but often gets a bit confused.

It’s not uncommon for the service to spin off into a wildly unrelated area of function name offerings after you type the dot that separates the function category from the function name.

Custom Column window in the Power Query Editor showing beginning of a formula and the Intellisense context menu expanded with suggestions that do not match the typed-out function.

Another strange behavior is when you are typing a function (say the table function named “ColumnNames”), pressing Tab when selecting the offered function will unexpectedly double up the function category name.

Custom Column window in the Power Query Editor showing an incorrect formula =TableTable.ColumnNames.

The trick to keeping IntelliSense on the right track is to NOT TYPE THE DOT that falls between the category and name.  It sounds odd, but it actually works.

Custom Column window in the Power Query Editor showing beginning of a formula with no dot and the Intellisense context menu expanded with correct suggestions.

Once you see the function you’re interested in, arrow down to its position and hit the TAB key to complete the statement.

BONUS TIP – Accessing Power Query Function Help

If you ever need help with understanding the layout or requirements of a specific function, write a formula in the Formula Bar that starts with an equals sign, then has the function name WITHOUT parentheses.

This will present the same content found on the Microsoft Power Query M Function Library website.

Fragment of the Power Query Editor window showing the definition of the =Table.TransformColumnTypes function.

Featured Course

Master Excel Power Query – Beginner to Pro

Power Query is essential for Excel users who work with lots of data. This course teaches you how to use Excel in Power Mode and create meaningful reports with far less effort.
Learn More
Power Query Course cover

#4 – Zoom in and Out Using Keyboard Shortcuts

One of the main frustrations Power Query users with large monitors have is that the fonts and information may be too small to easily read.

Fragment of the Power Query Editor Window with very small text.

An easy way to zoom into the content is by pressing the CTRL-Shift-plus and CTRL-Shift-minus keys.

Shortcuts to zoom in (Ctrl + Shift + Plus) and zoom out (Ctrl + Shift + Minus) in Power Query.

Each press of these key sequences will either increase or decrease the zoom factor of the Power Query Editor data interface.

Fragment of the Power Query Editor Window with zoomed-in text.

This is especially helpful when working with the Advanced Editor and viewing the query’s M code in its entirety.

Power Query Advanced Editor window with M code.

BONUS TIP: When operating in regular Excel, you can use the CTRL key in conjunction with spinning the mouse wheel to zoom in and out of a spreadsheet.  Try it out; it’s crazy fun.

#5 – Sort Columns Alphabetically Using Power Query

When working with a table that has many columns, and you need to sort a table’s columns in alphabetical order, perform this set of quick steps to achieve a sorted list of column names.

  1. Start with an unsorted data set.
Fragment of an Excel table with column headers from the Nato alphabet in random order.
  1. Bring it into Power Query.
  2. Select Home (tab) → Manage Columns (group) → Choose Columns.
Fragment of Power Query Editor menu with the Home tab and Choose Columns button highlighted.
  1. Sort the displayed list by Name instead of Natural Order.
Fragment of the Choose Columns window in Power Query Editor with the Sort Order expanded to show Natural Order and Name, with Name selected and highlighted.
2 versions of the Choose Columns window with an arrow pointing from left to right. The left window has columns sorted in natural order, the right window has columns sorted alphabetically by name.
  1. Click OK and enjoy your newly sorted table.
Fragment of an Excel table with column headers from the Nato alphabet in alphabetic order.

#6 – Get All Excel Sheets by Selecting the Folder

When working with the “From Excel Workbook” data connector, an initial list of all sheets, tables, and ranges is presented in a Navigator window.  This allows the user to select which worksheet, table, or range of data to be brought into Power Query for transformations.

Power Query Navigator window. Query connected to an Excel workbook called Financial Statement 2023.xlsx containing multiple sheets. The Sales sheet is selected and visible in the Preview on the right.

But what if multiple items from this list need to be brought into Power Query?  Creating a separate query for each item would prove tedious and time-consuming.

Checking the box labeled “Select multiple items” will allow checkboxes to be used to select as many or as few items as needed.

Fragment of the Power Query Navigator window. "Select multiple items" option is checked and highlighted. Four sheets - Cost Codes, Expenses, Sales, and Tax Tables are checked.

But what about the situation where new sheets are being periodically added and the user always needs every sheet in the file?

The trick is to select the folder icon next to the file’s name.

Fragment of the Power Query Navigator window. "Select multiple items" option is unchecked. File name - Financial Statement 2023.xlsx - next to a folder icon is selected.

This will bring in a table of meta-data information about the objects on the list.

Fragment of the Power Query Editor showing the contents of the file. Includes columns: Name, Data, Item, Kind, and Hidden.

From here, creative filtering and selecting can reduce the list down to the items needed, then let the transformations FLY!

You can apply the same logic – use the folder – when connecting to other file types, such as PDF.

#7 – Change and Monitor Table Names from the QAT

When working with proper Excel Data Tables, it’s common to give the table a more memorable name.  This makes writing formulas and referencing columns more intuitive.

Fragment of an Excel workbook, showing the ribbon and part of the worksheet. Table Design tab is highlighted in the menu. Worksheet contains an Excel table. An arrow leads from the table to the Table Name: Products.

However, when you need to remember what you named a table, you must perform the following steps:

  1. Click on the table
  2. Select the Table Design ribbon
  3. Examine the Table Name field in the Properties group.

To ALWAYS see the table’s name, add the Change Table Name feature to the QAT.

  1. Right-click any button on the QAT and select “Customize the Quick Access Toolbar”.
Fragment of the Excel ribbon including toolbar, showing the Quick Access Toolbar context menu with "Customize Quick Access Toolbar" highlighted.
  1. In the Customize Toolbar section, set the “Choose Commands From” dropdown to “Table Tools | Table Design Tab”.  Select “Change Table Name” from the list and press the Add button (middle) to place the option on the right-side table.  Click OK when done.
Fragment of Excel Options window with the Customize Toolbar tab selected and highlighted. Two list boxes - the one on the left showing available commands, filtered to "Table Tools | Table Design Tab", with "Change Table Name" selected and highlighted on the list with an arrow pointing to "Change Table Name" in the box on the right. "Add" button highlighted between the two lists.

Alternatively, you can right-click “Table Name” in the Table Design Ribbon and click “Add to Quick Access Toolbar” to add the feature to the QAT.

Fragment of the Excel ribbon showing Table Name: Products and the context menu with "Add to Quick Access Toolbar" highlighted.

Either of these methods will add the below-displayed information onto the QAT.

Fragment of an Excel workbook showing the ribbon on the Home tab, with Quick Access Toolbar on the bottom displaying the name of the table (Products) visible below in the worksheet.

It’s now much easier to keep track of the selected table’s name as well as update the name when needed.

Featured Course

Fast Track to Power BI

Stay ahead of the game in 2024. Get access to our best-selling Power BI course now and become a highly sought-after Power BI professional. This course gets you started in Power BI – Fast!
Learn More
Power BI Course image

#8 – Copy and Paste Queries Between Excel Files

One of Power Query’s great strengths is to reduce the amount of repetitive work.  Writing a query and then repeating all the transformation steps with a single click of the “Refresh” button.

But what if you have a lengthy query with dozens of steps, and you need to build a query in a different file that serves the same or similar purpose?

Power Query allows users to copy and paste queries from one file to another.  It’s easy.

Open a file with the source query and display the Queries & Connections panel (located on the Data ribbon).

Fragment of the Excel ribbon with the Data tab and Queries & Connections button highlighted.

From the Queries & Connections panel, right-click the needed query and select Copy.

Fragment of the Queries and Connections pane in Excel showing 4 queries: Jan 2008, Feb 2008, Mar 2008 and Transactions. Jan 2008 query is selected. Visible context menu with Copy highlighted.

Navigate to the destination file, activate the Queries & Connections panel, then right-click in an empty part of the panel to select Paste.

Fragment of an empty Queries and Connections pane in Excel. Visible context menu with Paste highlighted.

Now bask in the glory that is your new, complete query.

Fragment of the Queries and Connections pane in Excel showing 1 query: Jan 2008.

BONUS COPY TIP

If you copy a query that is dependent on the output of other queries (known as “feeder queries”), the selected query along with all “feeder queries” will be pasted to the destination file.

Fragment of the Queries and Connections pane in Excel showing 4 queries: Jan 2008, Feb 2008, Mar 2008 and Transactions. Transactions query is selected. Visible context menu with Copy highlighted. Arrows pointing from the Jan 2008, Feb 2008 and Mar 2008 to the context menu.
Fragment of an empty Queries and Connections pane in Excel. Visible context menu with Paste highlighted.
Fragment of the Queries and Connections pane in Excel showing 4 queries: Jan 2008, Feb 2008, Mar 2008 and Transactions. Below a caption "All Queries are Pasted".

#9 – Add Excel Slicers to Tables

Power Query’s output often takes the form of proper Excel Tables.  Because of this, Slicers can be used to easily filter the table.

If you haven’t heard of Slicers, think of them as super-fast filters.  Instead of wandering through lengthy lists of checkbox items, Slicers present easy-to-read and easy-to-select buttons.

Take the below Power Query output table for example.

Fragment of an Excel worksheet with an official Excel table including columns: Date, Inventory Item, Sold By, Cost, Price, Commission.

If we wish to filter by the [Sold By] column, we could use the old-school checkbox filter controls.

Fragment of an Excel worksheet with an official Excel table showing the Filter on the Sold By column.

Or we could add a Slicer to perform the filtering.

Fragment of an Excel worksheet with an official Excel table and a Slicer based on the Sold By column above the table, with an arrow pointing to it.

To add a Slicer to a proper Excel Data Table, click the table, then select Table Design (tab) – Tools (group) – Insert Slicer.

Fragment of the Excel ribbon with Table Design tab and Insert Slicer button highlighted.

Select the field (or fields) you wish to use as Slicer controls.

Insert Slicers dialog box displaying column names, with a checkmark next to Sold By.

The initial Slicer offering is rarely in the size, location, or color that you wish it to be.

Fragment of an Excel worksheet with an official Excel table and a Slicer based on the Sold By column in front of the table.

Use the resize handles to shape the Slicer to the desired height and width, then move the slicer to the desired location.

You can use the Slicer ribbon to apply various other customizations, like color, name, heading customization, number of columns, etc.

Excel ribbon with the Slicer tab selected.

Clicking one of the buttons will instantly filter the table.

Fragment of an Excel worksheet with an official Excel table and a Slicer based on the Sold By column above the table, with the name Jane selected in the Slicer and the table's Sold By column filtered to only Jane.

#10 – Adjust Power Query Refresh Settings

Suppose you have a Power Query result that needs to be updated each time the Excel file is opened.  Or how about every twenty minutes while the file is open?

We can’t expect the user to click the Refresh button every twenty minutes.

This refresh process can be automated by opening the Queries & Connections panel (located on the Data ribbon), then right-clicking a query and selecting Properties.

Fragment of the Queries and Connections pane in Excel showing 3 queries: Sales-May, Sales-Jun, Append1. Append1 query is selected. Visible context menu with Properties highlighted and an arrow leading from Append1 query to Properties.

In the Query Properties dialog box, select the best refresh option that meets your needs.

Query Properties dialog box with a frame around Refresh control options. Checks next to "Enable background refresh", "Refresh every 20 minutes", "Refresh data when opening the file" and "Refresh this connection on Refresh All".

Each query can be controlled differently, giving you maximum flexibility when updating workbooks with multiple queries.

Closing Thoughts

The above tips are but a tiny fraction of ways you can optimize your Power Query workflow while at the same time dazzling your supervisors and coworkers.

For more information on how you can become the best Power Query user imaginable, check out my courses listed below for both beginners and advanced users.

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.