Quickly Learn Excel’s Power Query Tool [Guide 2022]

Quickly Learn Power Query Now!

The goal of this guide/tutorial is to provide you with a great starting point in understanding what Power Query can do and how to use its basic functionalities. Think of it as an abbreviated version of “Power Query for Dummies”.

Power Query is a tool that is now part of Microsoft Excel and it relies mostly on the user clicking buttons (not manually writing coding) to perform your desired automation steps. It is a fantastic replacement for replicating solutions that were previously only possible with large amounts of VBA coding (macros).

There are many advanced capabilities that Power Query can carry out, but those can be researched more precisely outside this guide. After spending a few minutes reading this post, you should be more than equipt to hit the ground running and build your first automated solution using Power Query in Excel!

If you would like to follow along with the simplistic example data used throughout this guide, click the button below to directly download the Excel example file.

Power Query Video Tutorials

If you are more into learning via video, here are some of my favorite Excel YouTubers providing an introduction to Power Query:

What Does Power Query Do?

What is Power Query?

Power Query is a free solution Microsoft developed for Excel to help users automate merging data, cleaning, and transforming it through automated steps. The brilliance of Power Query is beginners can do this all without needing to code anything, yet there is the ability for advanced users to code more complicated data transformations.

Power Query allows you to load multiple data sets into its editor. Within the editor, there are various Ribbon buttons you can click to remove duplicates, delete rows, format dates, etc… just to name a few. Many data-manipulating automations that were only possible through VBA coding can now be easily replaced via the Power Query tool.

Once you have created all your automated actions (aka Steps), Power Query stores everything in the backend of your Excel file and sends your final result into a new Excel Table within your spreadsheet. The next time you need to update your spreadsheet with new data, you simply click the refresh button and all your steps are automatically applied!

A Brief History Of Power Query

Power Query started out as a Microsoft-Published add-in for Excel back in 2010. The add-in’s capabilities became directly integrated into Excel starting with the release of Excel 2016. The Power Query add-in user interface was collapsed and now resides in the Data tab within the Get & Transform.

In 2022, Microsoft built in the Power Query functionality to the Mac version of Excel. Microsoft is still working on getting this capability into Excel Online.

Power Query is also now a pivotal tool for prepping data to load into Power BI dashboards.

What Can Power Query Do?

Microsoft summarizes how data flows through Power Query using 4 phases:

1. Connect – Pull in data from a spreadsheet, local file, database, or online webpage
2. Combine – Integrate data from multiple sources to get a consolidated data set
3. Transform – Clean, format, delete, and add to your data with automated steps
4. Load/Export – Send your finished output into a spreadsheet or Data Model and periodically refresh it

Where Is Power Query in Excel?

Unfortunately, Microsoft had reservations about the scariness of the tool’s original name, “Power Query” when they were embedding it directly into Excel’s Ribbon. This caused them to change the name to a more “tame” name of Get & Transform Data (many of the Excel MVPs tried very hard to dissuade Microsoft from doing this).

Since Power Query had been around for a few years, the majority of all the published documentation whether it be books, videos, or articles all utilized the original name for the tool. Making matters worse, Microsoft didn’t completely switch out all traces of the name as we still have the Power Query Editor name in use rather than the “Get & Transform Editor”.

Needless to say, the discoverability of Power Query has been horrendous at best. So how do you get to Power Query?

If you think back to the 4 phases of Power Query, you start by giving it some data. These data connectors are found in Excel’s Data tab. Once on the Data tab, you will see the Get & Transform Data button group label on the far left-hand side of the Ribbon.

If you want to open the Power Query Editor directly, there is a button available to launch it under the Get Data menu button. The editor itself will launch in a separate application window and unfortunately disable your Excel spreadsheet while the editor is open.

How To Get Data Into Power Query

There are a bunch of data sources (aka connections) you can utilize to send raw data into Power Query to then start manipulating. Microsoft continues to add to its long list of dedicated connection dialogs. Let’s look at some of the most popular.

Examples Of What You Can Connect To

The great thing about Power Query and its integration within Excel is it’s very easy to set up a connection to your data source. Since you are trying to get a basic understanding of how Power Query works, we are going to stay within Excel and work through setting up connections to Tables inside your spreadsheet that you have copied in with report data from another system.

Guide Example with Excel Tables

Throughout this guide, we are going to work with the above two tables that get updated every month. We will work through combining these two data tables into one clean dataset so we can easily pivot or summarize the data.

One important thing you should keep a habit of doing is renaming your table objects to something meaningful. You can do this through the Table Design tab that appears in the Ribbon after you have selected a cell in a Table Object (shown above). The table name should serve as a reminder of what is stored inside the table so you don’t need to go back to your spreadsheet once you are inside the Power Query Editor.

Establishing A Connection To Your Data

Since we will be using Excel Tables as our data source, we can simply use the From Table/Range button to instantly create a Power Query connection.

You can do this by either:

1. Data Tab >> From Table/Range button
2. Right-Click on a Table Object cell and select Get Data From Table/Range…
3. Data Tab >> Get Data menu >> From Other Sources menu >> From Table/Range button

After you have navigated to the proper connection button you will either need to fill out the dialog box that appears or in this case, the connection will automatically be generated and the Power Query Editor will automatically open.

You’ll note that a preview of the data is shown and also Power Query took an educated guess at how the data should be formatted. You can also see a Query was set up with your table’s name in the left-side pane.

Let’s pause here and take some time to review the different parts of the Power Query Editor.

Overview Of Power Query Editor

The Power Query Editor opens in its own application window. Unfortunately, while it is open you cannot navigate back to your Excel spreadsheet and explore/modify it. This feels like a bug, but Microsoft has acknowledged it is by design and to date has refused to allow us to work in both Power Query and Excel at the same time. Hopefully, someday this will change.

The editor should feel familiar as it takes many UI elements from Microsoft Office. There are essentially 4 zones you will notice in the editor

1. Ribbon
2. Query Pane (left-hand side)
3. Query Settings Pane (right-hand side)
4. Table Preview area (main working area)

Let’s dive into each area a bit further so you get an introduction to the purpose of each of these areas.

The Ribbon

• Home Tab – Access many popular actions including Combining Queries, Find & Replace, Remove/Modify rows or columns, and much more!
• Transform Tab – Various buttons to manipulate and/or populate your data. This is where many of the data cleanup actions are stored.
• Add Column Tab – Various buttons to add additional columns of data to your query table
• View Tab – Mostly settings you can toggle on/off to modify the user interface of the editor

Query Pane

Every table of data you connect to Power Query creates something called a Query. These Queries will reside in the Query Pane where you can modify the data. You can make copies of Queries, make your own Query from scratch, or even merge two or more queries to create a completely new Query!

Table Preview

This area will show you a sample preview of your data output based on your currently selected Step in the Applied Steps list box. There are also automations you can have Power Query write for you by choosing to filter a column a specific way or right-clicking on the target column.

Editing The Automation Code

• Formula Bar – The Power Query Editor has a formula bar similar to Excel where you can edit the coding of the currently selected Step within the Applied Steps list box
• Coding Editor – If you wish to view/modify the entirety of the automation you have created, you can open the Advanced Editor which will display all the code that was written by Power Query. Unfortunately, this editor leaves much to be desired compared to your typical coding editor.

Properties Section (in Query Settings Pane)

The Properties section allows you to rename your Query or provide a description to document what your query automation does.

Step Selector (in Query Settings Pane)

Every action you take in Power Query creates a Step. Each Step can be viewed or modified within the Applied Steps list box. You can change the order of your steps, delete them, or change their name to something more meaningful. Most Steps has a user-friendly dialog box that can be utilized to manage it or you can use the formula bar/Advanced Editor to manually modify the code.

Help Button

If you would like to view Microsoft’s help articles, you can click the blue question mark icon. The button links directly to Microsoft’s official Power Query support website.

Merging Two Tables Together

Now that you are familiar with the Power Query Editor, let’s get back to our example data and start working with Power Query!

If you recall the 4 phases of Power Query that we discussed earlier in this article (Connect, Combine, Transform, and Load/Export), we are beginning the Connect phase.

As a reminder, below are the two tables we are working with and want to load into Power Query.

Let’s start by connecting the Prior Month Report table (named TBL_PriorMonth) into Power Query.

Simply select a cell within the table (example Cell C5) and click the From Table/Range button within the Data tab. These steps are shown in the below screenshot.

After clicking the From Table/Range button, the Power Query Window should open automatically and create your first query.

You’ll notice several things Power Query did for you to get you started:

1. A query was created with the same name as your table (in this case TBL_PriorMonth)
2. Two steps were created
   1. Source – This is the code that connects this query to the Excel table
   2. Changed Type – Power Query used AI to determine the data type of each of your columns (date, number, text, etc…)
3. A preview of your data after the Changed Type Step is currently showing

We could start manipulating our data now, but let’s go back and connect our second table to Power Query. We ultimately want to merge both tables into a consolidated query before we start cleaning up the data.

To save our query, do the following steps:

1. In the Home tab, open the drop-down menu in the Close & Load button
2. Select the Close & Load To… button
3. Ensure the Only Create Connection option button is selected
4. Click OK

After this, Power Query will close itself and you will be taken back to Excel.

Now, repeat the same steps to connect the Current Month Report table (TBL_Curr Month) to Power Query. Once the Power Query Editor reopens, you should see a second query created similar to the first one.

Finally, let’s merge these two tables into a new table that we can then proceed to reconfigure to suit our needs.

There are two options to combine queries

• Merge Queries – Use an ID column to bring columns from another query into the other query
• Append Queries – Take rows from other queries and append them to the bottom of the query (typically will have the same heading names but not necessary)

Since we are working with two iterations of the same report, we are going to use the Append functionality to combine our queries.

To do this, navigate to the Append Queries as New button and select both table names, then click OK.

After you have executed the Append functionality, you will see an additional query created called Append1. The only Step created will be a Step named Source which stores the coding of the Combine function (also shown in the Formula Bar).

Congratulations, you have already accomplished some automation using Power Query. Let’s dig in further and start cleaning up this data set so we can be more efficient in reporting it out.

Automate Cleaning Your Data

There are many, many different data-cleansing or manipulating actions you can take inside of Power Query. Some of these automation capabilities include:

• Removing blank rows or duplicates
• Splitting columns by a delimiter
• Filling down blanks
• Getting the “age” between two data columns

Since there are just too many functionalities to cover in this section, I’m going to go through some of the more popular actions you can take. This will provide you with a good foundation and if you have additional data cleansing/manipulation needs, simply Google it along with the phrase “Power Query”. Chances are there will be a solution that can easily be found. Let’s get into it!

Setting Data Types

One issue can run into while merging reports is having different data types within the same column. This can easily mess up formulas or charts relying on the data.

Power Query allows you to designate the type of data that should be in a particular column. Power Query will try to guess at what the data should be to start out or if you are merging from other queries, it will rely on the designations from the source queries.

Changing a column’s data type is very easy. Simply click the little icon next to the column name and select the type you wish to designate.

If there are any conflicts with what you designated, an error indicator will appear.

In our example, we will ensure the DATE column has a date data type and the REVENUE column has a data type of currency.

You’ll see that when the DATE column was updated, an error was flagged. This is because there was a date of Feb 30th and that is not a valid date because there are only 28 days in February in 2022. We will handle this error in a later section.

You’ll also note that if you do the same action types consecutively, Power Query may be able to combine them into a single step. Since we changed two column data types back to back, Power Query coded those two actions into a single step. You can see how this was written in the Formula Bar.

Find/Replace

Find/Replace can be a very powerful action while cleaning your data. Power Query simply called this Replace Values. In order to add a find/replace automation to your steps, do the following:

1. Select the column you wish to apply the find/replace to (click the header and the column should turn green)
2. Go to the Transform tab in the Ribbon
3. Click the Replace Values button
4. Fill out the Replace Values dialog box
5. Click OK

In our example we are going to set up the following replacement Steps in the PRODUCT column:

• Find Widget A —> Replace with Steel Gears
• Find Widget B —> Replace with Plastic Gears

Note, unlike Excel’s find/replace functionality, you cannot use special characters to perform broader replacements (aka wildcard searches). This becomes evident if you try to use an asterisk (*) as part of your Find value. Don’t worry, we’ll cover this functionality in the next section as Power Query handles wildcard finds a bit differently than Excel.

Extracting (Find/Replace Wildcard Search)

“Extracting” is Power Query’s way of performing more specialized Find/Replace actions. If you click the Extract menu button within the Transform tab, you will see a variety of actions you can perform.

In our example query, we are going to look to do the following in the CUSTOMER column to clean up the customer names:

• Remove all text after “ -” (space + dash)
• Remove all text after “_” (underscores)

In Excel, these types of clean-up would utilize the Find & Replace dialog and warrant the need to use an asterisk. In Power Query, this same data cleansing can be done using Text Before Delimiter, Text After Delimiter, & Text Between Delimiters.

For our example, we want to keep the text before our delimiter, so we will need to click the Text Before Delimiter button. Then enter in your delimiter character or string of characters and click OK.

After the two Extract steps have been created, you will instantly see the customer data straightened up in the CUSTOMER column.

Delete Columns

Many reports we utilize have extra columns that are not necessarily useful to us. This means having to go through and delete them every time we get a new report.

Luckily, with Power Query, we can automate deleting specific columns based on their heading name in seconds. Simply do the following to delete a column:

1. Select the column you wish to delete (click the header and the column should turn green)
2. Go to the Home tab in the Ribbon
3. Click the Remove Columns button

In our example query, we will remove the CUSTOMER NO column as the customer ID number is not useful to us while we are analyzing our data.

You’ll see below that the CUSTOMER NO column is removed from the query preview area after we have removed it.

If you need to delete a bunch of columns you can use your ctrl or shift key to select multiple columns before clicking the Remove Columns button (similar to how you select multiple columns in Excel).

If you individually delete multiple columns back-to-back, Power Query will use its intelligence to merge the deletions into a single step.

Reorder Columns

If you would like to move columns around in your query output, you can click and drag the header to the column position you want (note you can select multiples and drag using either your ctrl or shift keys). If you would like to use a Ribbon button, you can do the following:

1. Select the column you wish to reposition (click the header and the column should turn green)
2. Go to the Transform tab in the Ribbon
3. Click the Move menu button
4. Select the moving action you would like to take

For our example query, let’s move the PRODUCT column to be in between the CUSTOMER and REVENUE columns

If you individually move columns back-to-back, Power Query will use its intelligence to merge those movements into a single step.

Removing Rows

Removing rows in Power Query requires a mind shift in comparison to Excel. Instead of physically deleting rows, Power Query utilizes filtering out based on criteria.

This makes sense if you think about what Power Query is ultimately giving you. When you get to the point of loading your query’s output back to the spreadsheet, anything that is filtered out simply is not loaded.

Filtering out data is exactly the same as Excel. Simply click the down arrow icon next to the column heading and chose how you wish to filter the data. After you click the OK button, a step will be created for the filter action.

For our example query, let’s filter out EDF Corp as they are a subsidiary of our company and we do not want to include their intercompany revenue in our data.

As an alternative to filtering, Power Query does have some pre-configured actions to remove rows. If you navigate to the Home tab and open the Remove Rows menu, you’ll see various actions you can take to remove rows.

Remember that Date error we had when we were formatting the column data types (Feb 30th doesn’t exist)? Let’s remove that from our data by Selecting the DATE column and using the Remove Errors button.

Now we know our data will have legitimate dates in the output so we can cleanly summarize our data over time.

Sorting

IF you would like to reorder your data in Ascending or Descending order, you can very easily do it either by:

• Select the column and go to Home Tab >> Sort >> Ascending/Descending buttons
• Using the column’s filter button

For our example query, let’s sort the DATE column in Ascending order so the oldest dates are at the top of the table.

Managing Your Automation Steps

As you add more and more steps to your query, there will likely be scenarios where you need to manage them a bit post-creation.

Editing Old Steps

When you select a specific Step, the query preview data will display the output as of that current step. This allows you to travel backward/forward in time through your automation and visually see the results.

Also, the Formula Bar will display the coding for that specific step. You are more than welcome to modify the coding directly in the Formula Bar if need be (just make sure you don’t have any typos). Click the checkmark button next to the Formula bar once you are finished to confirm the change.

Right-Click Step Menu

Most of the management of your Steps is done through the Right-Click Menu. If you right-click on the Step you wish to modify, you will see many options available. This menu will allow you to

• Move the Step
• Delete the Step
• Insert a new Step
• Rename the Step
• Edit the Step (if applicable)
• Copy Steps into a new Query (Extract Previous)
• Modify Step Properties

Documenting Steps

Clearly documenting the purpose of each Step is a very good habit to get into. Chances are you will have many Steps created in your query and you’ll want to prevent any chance of forgetting the reasoning behind a certain Step. Documenting your Steps also helps others better understand what is going on behind the scenes.

The best place to provide your Step description is through the Step Properties dialog box. This dialog box is accessed by right-clicking the Step name and selecting Properties.

In the Step Properties dialog box, you can rename your Step to be more meaningful and also provide a description. I always try to include the action the Step is taking and also the reasoning behind the action in my descriptions. The idea is to quickly jog your memory as to why the Step was created in the first place.

When you add a description to a Step, an info icon appears next to the Step name in the Applied Steps list box. Hovering your mouse over the info icon reveals your Step description in a tooltip.

Writing/Editing Custom Formulas

Power Query utilizes its own formula language simply called “M”. While it has many of the same types of functions as Excel, the function names and input order may be different. For example, Power Query’s version of Excel’s Right() function is Text.End.

Since this is a quickstart guide I obviously won’t be teaching you how to code in M, but I do want to share with you the structure of how the code is written so you can do basic editors and better understand the solutions you might come across through your Google searches.

If you would like to learn more about the various functions available in the M language you can visit the official Power Query M Language Function Library site.

One final note before we start as this is very important to keep in mind. Power Query’s M language IS case-sensitive. This means that “a” is not the same as “A”. Or in the case of functions “text.end” would error out because it wasn’t written “Text.End”. This was difficult for me to remember as Excel’s VBA language is not case-sensitive.

We will start by opening up the Advanced Editor and reviewing the code that Power Query wrote for us in our example query. Remember you can access the Advanced Editor via the Home Tab of the Power Query Editor.

The high-level code structure of a query in M code is as follows:

• The word “let” (indicates the code is beginning)
• code to reference the initial source data
• each Step name in the query in the order it needs to be executed along with the function it is using
  • Each new Step must begin with a # and end with a comma (except the last Step will have no comma)
• The word “in” (indicates the code is finishing)
• The name of the Step that is the final output of the Query

You’ll note that in the above graphic I highlighted in yellow the fact that every Step references the previous Step’s name in its formula. This is how the code maintains the order in which the Steps are executed. You will need to pay close attention to this if you are renaming Steps or moving steps around in the Advanced Editor. Personally, I do all my maintenance of Step names, order, and descriptions using the Applied Steps list box so I don’t have to worry about typos.

That is as far as we will go in this guide concerning writing code. Hopefully, this will give you a good understanding of what to look out for and where to go if you need to manually code something in either the Formula Bar or the Advanced Editor.

Exporting Your Data Back To Excel

Once you are finished with all your steps, you’ll be ready to output your data back into Excel. You can do this by:

1. Navigate to the Home tab
2. Click the Close & Load button

If this is your first time loading the query to the spreadsheet, a new worksheet will be created with the sheet name set to the query name.

The data will also be stored in an Excel Table object.

You will also see a new contextual tab called Query whenever you click into an Excel table that is connected to a query from Power Query. This tab will give you a few options that can help you easily maintain the query going forward. Most notably is the Edit button which opens up the Power Query Editor.

Refreshing Your Power Query Automation

Now that you have your Power Query steps all set up, you can enjoy the time saved going forward. After you’ve either brought your data into your Excel file or the data source you are connecting to has updated, you’ll just need to click the refresh button to kick off your automation.

While your query is refreshing, you will see a notification in the Status Bar (bottom left of Excel window). You can keep working on other parts of your Excel workbook while your query updates.

There are several different ways you can access the Refresh button, so I’ll cover them all below. They all do the exact same thing, so it is really personal preference as to which method you use.

Ribbon Method

1. Select a cell in your Power Query output table
2. Open the Query tab that should appear in the Ribbon (or navigate to the Data tab)
3. Click the Refresh button

Right-Click Method

1. Select a cell in your Power Query output table
2. Right-Click on the cell to open the
3. Click the Refresh menu button

Queries & Connections Method

1. Navigate to the Data tab
2. Click the Queries & Connections button
   • The Queries & Connections Pane will appear on the right-hand side
3. Click the Refresh icon next to the respective Query name

 

 

 

 

Power Query Editor Method

1. Open the Power Query Editor (Data Tab >> Get Data menu button >> Power Query Editor button)
2. Ensure the Home tab is activated in the Power Query Editor window
3. Click the Close & Load button

The Best Power Query Books

I originally learned how to use Power Query by purchasing a book that summarized everything about the tool. Back then, there weren’t great centralized resources so a book was the best way to get a good understanding of the tool. I still think reading through a book is a great way to develop a good foundation for Power Query skills.

My personal favorite book out there is by Miguel Escobar and Ken Puls. They originally wrote M is for Data Monkey which is the book I learned from. In 2021, they released Master Your Data with Power Query in Excel and Power BI which is an updated version of their original book.

Below are some Amazon links to some of the top Power Query-related books out there.

Note, I get a small commission from Amazon if you purchase a book or anything else from Amazon using the above book links within 24 hours after clicking (at no extra cost to you). I appreciate you supporting this website if you choose to use my Amazon affiliate links!

Additional Power Query Posts You Can Learn From

Here are some additional Power Query articles you might be interested in:

• Override Values With Formula Using Power Query

I Hope This Helped!

Hopefully, I was able to provide you with a solid foundation where you understand Power Query’s purpose and can start exploring the many strengths it has to offer. I want to make this the best introduction to Power Query I can as I think this is a very important tool in Excel that is far too overlooked. If you have any questions about a certain section or suggestions on how to improve this guide, please let me know in the comments section below.

Format Excel Charts Like a Pro!!!

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Get These Hacks Now For Free!

Keep Learning

Explore more →

How To Use A VBA Macro You Found On The Internet

You Finally Found It! Yes, I’ve been there! You’ve spent days scouring the internet to find a solution to a...

Learn VBA Macro Coding Basics In Excel [2023 Guide]

No Coding Experience? No Problem! First I want to congratulate you because if you are taking the time to read...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.