It’s now time to bring this Power Query series to a close. And where better place to end than with some tips and tricks to help you to succeed.
Change the default Close & Load options
Power Query’s default Close & Load options assume we want to load the data into an Excel table. As you progress with Power Query, this will become less and less the norm. Actually, as you progress along your “Power” journey, you’re more likely to use data within Power Pivot, which requires loading into the data model.
To change the default Close & Load options, open the Power Query editor, then click File -> Options and settings -> Query Options.
In the Query Options window, select the Global: Data Load option.
In this dialog box we find the specify custom default load settings option:
- Load to worksheet – if checked the data loads to the worksheet, if unchecked, it loads as a connection only.
- Load to Data Model – if checked the data loads into the data model, if unchecked, nothing loads into the data model. To use the data in Power Pivot, this option must be checked.
Selecting use standard load settings is equivalent to checking load to worksheet, and unchecking the load to Data Model.
Give steps a meaningful name
I am guilty of not following my own advice here. Renaming steps to have a meaningful description is such a time saver. It may not seem it at the time, but in the long-run, it will be. It is so easy to have queries where the applied steps look like this:
What did each of those steps do? There are way too many to remember, so we’ll have to keep looking back at the Preview Window and Formula Bar in an attempt to work out what we did. If only we had renamed the steps when creating the query.
Renaming the steps is easy — Right-click on the step name and select Rename from the menu.
This also changes the name of the step in the Advanced Editor.
Always have the Formula Bar visible
The Formula Bar is the perfect balance between the complexity of the Advanced Editor window and the simplicity of the main user interface.
Seeing the M code helps us to:
- Learn and appreciate what Power Query is doing in the background
- Quickly make small code edits where needed
If you’re coming from an Excel world, the Formula Bar is a familiar element of the window. We might not understand everything within it, but that doesn’t mean it’s not useful.
To enable the Formula Bar click View -> Formula Bar from within the Power Query editor.
Prevent auto-detect data type
Every time we import data, Power Query guesses the type of data each column contains. Based on this guess, a new step is added to the applied steps list.
Also, if the data is unstructured, such as a CSV file, or a named range, the first row will be promoted to the header row automatically.
We can find ourselves regularly deleting these steps. Therefore, it may be faster and easier to disable auto data type detection Within the Power Query editor, click File -> Options and settings -> Query Options.
In the Query Options window, select the Current Workbook: Data Load group.
Remove the tick from Automatically detect column types and headers for unstructured sources, then click OK.
Any queries created will now include only the Source step. It’s up to us to decide if and when to promote headers and data types to use.
Use Go To Column
While working with a table containing lots of columns, it can be time-consuming to find the column we want; moving back and forth with the horizontal scroll bar is not fast. Using the left and right cursor keys can be a useful, but there is a better way, called Go to Column.
The Go to Column icon is hiding away on the View ribbon (which is the one we rarely use). Click View -> Go to Column.
The Go to Column dialog box is simple and easy to use.
There is no multi-select or complex selections, simply select the column name, then click OK (or double-click the column name to avoid clicking OK).
The box at the top allows us to search for any text string. Next to that is the icon to sort in alphabetical or natural order. Searching does not have any effect on the source data; it’s purely for finding a column.
Create folders for grouping queries
The more queries we have, the more cluttered the Queries & Connections pane and query list become. The examples we have used in this series have been relatively straight forward with just a few queries. But in the real world, there are likely to be a lot more.
It is an excellent idea to organize queries by putting them into groups. This is what Power Query does when it combines multiple files.
How you decide to organize the groups is up to you. But Parameters and Custom Functions are ideal candidates to put into their own folder. Also, as you advance in Power Query, you may start to create separate queries for extracting, transforming, and loading data; these are also useful organizational groups.
Folders can be created in the Queries and Connection pane, or the Query list.
Query list (in Power Query Editor)
If using the Queries list in the Power Query Editor, Right-click on the list and select New Group. Give the group a name, then click OK. A new folder will be created.
Drag the queries into the folders as required.
Queries & Connection (in Excel)
The process for creating folders in the Queries & Connections pane is slightly different. Right-click on the list and select Move To Group to reveal the options for creating folders or movings queries.
Delete steps until end
Building effective queries takes practice. Even then, we can find ourselves following a particular path, only to find out that the choice we made 15 steps ago wasn’t the best decision. Therefore, all the subsequent steps are built on a sub-optimal choice.
We could delete each step one-by-one to get back to the point before the decision; this will take a lot of clicks. Instead, we can achieve it with just two clicks :-).
In the applied steps list, right-click on the first step we wish to delete and select Delete Until End. All the subsequent steps will be removed.
Split a query into two
Sometimes the first part of a query is ideal as the first steps for another query. You might be tempted to build another query which contains the same steps… but that is a bad idea. Instead, it’s better to split the query into two parts, then use the first part as the source for both.
Right-click on the step where you want to split the query, then click Extract Previous (a slightly strange name in my opinion).
Give the query a new name, and click OK. You’ll now have two queries:
- The first part of the initial query
- The second part of the initial query, which uses (1) as its source
The first part of the initial query can now be used as the source for others.
Copy and paste queries to a new workbook
If you’ve created a useful query, why only use it once? It can be easily copied and pasted between workbooks.
In Excel’s Queries & Connections pane, right-click on the query, and click Copy. In the target workbook, right-click in the Queries & Connections pane and click Paste. It’s that easy.
This will paste the query itself, along with any other referenced objects. Obviously, if there are data sources used from within the source workbook, these will need to be repointed, to data in the target workbook.
Much like renaming steps, comments are one of those things we think we will get around to at a future point… but we never do. Within a few days, we have forgotten why we made certain decisions or what specific pieces of code are intended to do. When this happens, we waste a lot of time trying to understand our previous decision-making process. This is why comments are so important.
Inserting comments into the Advanced Editor / Formula Bar
A single-line comment in the Advanced Editor is denoted by two slashes ( // )
// This is a single line comment
Multi-line comments involve a mix of slashes and asterisks as shown below:
/* This is a multi-line comment: line 1 line 2 line 3 */
Comments entered using these methods are only visible in the Advanced Editor; they are not shown in the Formula Bar. Comments can be added into the Formula Bar, but once another step is selected, these comments are no longer visible.
Inserting comments into the step properties
If we right-click on any step, there is the Properties… option in the menu. Click that to enter the Step Properties dialog box, then enter the comment in the description box. Finally, click OK.
Any text entered into the Description box is included within the Advanced Editor as a single line comment.
That’s it for this Power Query series. If you’ve been following along, I hope you now feel capable enough to play around with Power Query. After you’ve been using Power Query for a bit, I recommend that you come back and work your way through again. It’s incredible the number of things which will make more sense the second time around.
Thank you for reading and following along 🙂
Power Query Series Contents
- Import Data
- Data Refresh
- Edit Queries
- Close & Load Options
- Using Parameters
- Basic Transformations
- Combine / Append Queries
- Import All Files in a Folder
- List All the Files in a Folder and File Attributes
- Import Data from the Current Workbook
- Import Data from the Web
- Unpivot Data
- Unstacking Data in a Column
- Lookup Values using Merge
- Change the Source Data Location
- If Statements for Conditional Logic
- Grouping and Summarizing Data
- Custom Functions
- Common Errors & How to Fix Them
- Tips and Tricks
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: