Written by Puneet for Excel 2010, Excel 2013, Excel 2016, Excel 2019
Imagine you work in a marketing department and have a monthly report that combines data from different sources: an Excel sheet with sales data, a CSV file with customer feedback, and an online database with website traffic.
Each source has different formats and might have errors, missing values, or unnecessary columns. Manually cleaning and combining this data monthly takes much time and effort.
By using Power Query, you can automate this process. First, you load the data from all these sources into Power Query. Then, you can remove any columns you don’t need, filter out any rows with errors, and format the data consistently.
You can also merge the different datasets into one clean table. Once you set this up, Power Query remembers all the steps.
Next month, when you get new data, you refresh the query, and it automatically applies the same cleaning and transformation steps. That saves your time and ensures your data is always ready for analysis.
What is Excel Power Query
Power Query is an Excel add-in that you can use for ETL. That means, you can extract data from different sources, transform it, and then load it to the worksheet. You can say POWER QUERY is a data cleansing machine as it has all the options to transform the data. It is real-time and records all the steps that you perform.
Power Query is one of the advanced Excel skills that you need to learn and, in this tutorial, you will be exploring power query in detail and will be learning to transform data with it.
Why Should You Use Power Query (Benefits)?
If you have this question in your mind, here’s my answer for you:
- Different Data Sources: You can load data into a power query editor from different data sources, like, CSV, TXT, JSON, etc.
- Transform Data Easily: Normally you use formulas and pivot tables for data transformations but with POWER QUERY you can do a lot of things just with clicks.
- It’s Real-Time: Write a query once and you can refresh it every time there is a change in data, and it will transform the new data which you have updated.
Let me share an example:
Imagine you have 100 Excel files that have data from 100 cities and now your boss wants you to create a report with all the data from those 100 files.
OKAY, if you decide to open each file manually and copy and paste data from those files and you need at least one hour for this.
But with the power query, you can do it in minutes. Feeling excited? Good.
Further in this tutorial, you will learn how to use Power Query with a lot of examples, but first, you need to understand its concept.
The Concept of Power Query
To learn power query, you need to understand its concept that works in 3 steps:
- Get Data – Power Query allows you to get data from different sources like the web, CSV, text files, multiple workbooks from a folder, and a lot of other sources where we can store data.
- Transform Data – After getting data in the power query, you have a whole bunch of options that you can use to transform and clean it. It creates queries for all the steps you perform (in a sequence, one step after another).
- Load Data – From the power query editor, you can load the transformed data to the worksheet or directly create a pivot table or pivot chart or create a data connection only.
Where is Power Query (How to Install it)?
Below you can see how to install access to the power query in the different versions of Microsoft Excel.
Excel 2007
If you are using Excel 2007, I’m sorry PQ is not available for this version so you need to upgrade to the latest version of Excel (Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).
Excel 2010 and Excel 2013
For 2010 and 2013, you need to install an add-in separately which you can download from this link and once you install it, you’ll get a new tab in the Excel ribbon, like below:
- First, download the add-in from here (Microsoft’s Official Website).
- Once you have downloaded the file, open it and follow the instructions.
- After that, you’ll automatically get the “Power Query” tab on your Excel ribbon.
If somehow that “POWER QUERY” tab doesn’t appear, there is no need to worry about it. You can add it using the COM Add-ins option.
- Go to File Tab ➜ Options ➜ Add-ins.
- In “Add-In” options, select “COM Add-ins” and click GO.
- After that, tick mark “Microsoft Power Query for Excel”.
- In the end, click OK.
Excel 2016, 2019, Office 365
If you are using Excel 2016, Excel 2019, or you have OFFICE 365 subscription, it’s already there on the Data tab, as a group named “GET & TRANSFORM” (I like this name, do you?).
Excel Mac
If you are using Excel in Mac I’m afraid that there is no power query add-in for it and you can only refresh an existing query but you can’t create a new one and or even edit a query (LINK).
Power Query Editor
Power Query has its own editor where you can get the data, perform all the steps to create queries, and then load it to the worksheet. To open the power query editor, you need to go to the Data Tab and in the Get & Transform ➜ Get Data ➜ Launch Power Query Editor.
Below is the first look at the editor which you will get when you open it.
When you open the Power Query Editor, you’ll see a preview of your data in the middle of the screen. This is where you can make changes and see the results immediately.
On the right side, there’s a pane called “Applied Steps”, which shows each transformation you make, like filtering rows or changing column names.
At the editor’s top are several tabs like Excel with different options. The “Home” tab has the most common options: removing columns, sorting data, and splitting columns.
The “Transform” tab offers more advanced options, such as pivoting and unpivoting data or adding custom columns.
Using these tools, you can manipulate your data exactly as needed. Once done, click “Close & Load” to bring your cleaned data back into Excel.
Now, let’s explore each section in detail:
1. Ribbon
Let’s look at all the available tabs:
- File: From the file tab, you can load the data, discard the editor, and open the query settings.
- Home: In the HOME Tab, you have options to manage the loaded data, like, delete and move columns and rows.
- Transform: This tab has all the options which you need to transform and clean the data, like merge columns, transpose, etc.
- Add Column: Here you have the option to add new columns to the data you have in the power editor.
- View: From this tab, you can make changes to the view for the power query editor and data loaded.
2. Applied Steps
On the right side of the editor, you have a query setting pane which includes the name of the query and all the applied steps in a sequence.
When you right-click on a step you have a list of options that you can perform, like, rename, delete, edit, move up or down, etc. and when you click on a step, the editor will take you to the transformation done on that step.
Look at the below where you have the total five steps applied and when I click on the 4th step it takes me to step four’s transformation where the columns name hasn’t changed.
3. Queries
The queries pane on the left side lists all the queries you have in the workbook right now. It’s basically one place where you can manage all the queries.
When you right-click on a query name you can see all the options that you can use (copy, delete, duplicate, etc.)
You can also create a new query by simply right click on the blank space on the queries pane and then select the option for the data source.
4. Formula Bar
As I said, whenever you apply a step in the editor it generates M code for that step, and you can see that code in the formula bar. You can simply click on the formula bar to edit the code.
Once you learn to use M code you can also create step by writing the code and simply clicking on the “FX” button to enter a custom step.
5. Data Preview
The data preview area looks like an Excel worksheet but there’s a little different than a normal worksheet where you can edit a cell or data directly. When you load data into the editor (we will do it in a while) it shows all the columns with the headers with the columns name and then rows with data.
At the top of each column, you can see the data type of the data in the column. When you load data into the editor the power query applies the right data type (almost every time) to each column automatically.
You can click on the top left button on the column header to change the data type applied to the column. It has a list of all the data types from where you can.
And on the left side of the column header there you have the filter button which you can use to filter values from the column.
Note: When you filter values from a column, the power query takes it as one step and lists it in the applied steps.
If you right-click on the header of the column you can see that there is a menu that includes a list of the options which you can use to transform the data and use any of the options and PQ stores it as a step in the applied steps.
Data Sources for Power Query
The best part of the power query is you have the option to get data from multiple sources and transform that data and then load it into the worksheet.
When you click on the Get Data in the GET & TRANSFORM you can see the complete list of data sources that you can get data load into the editor.
Now let’s look at some of the data sources:
- From Table/Range: With this option, you can load data into the power query editor directly from the active worksheet.
- Excel Workbooks: Connect to and import data from other Excel files, making combining and analyzing data from multiple workbooks.
- From Text/CSV: Import data from CSV files, commonly used for exporting data from various applications.
- From Folder: It takes all the files from the folder and load data from them into the power query editor. (See this: Combine Excel Files from a Folder).
- From Web: With this option, you get data from a web address, imagine you have a File that is stored on the web, or you have a web page from where you need to get the data.
- SQL Server: Connect to SQL Server databases, allowing you to access and analyze large datasets stored in your company’s database.
- Web Pages: Extract data from web pages, helpful in importing tables of data found on the Web Pages.
- Access Database: Import data from Microsoft Access databases, often used for smaller databases and local storage.
- SharePoint: Import data from SharePoint lists and libraries, commonly used in organizations for collaboration and document management.
- Azure: Connect to Azure services for cloud-based data storage and management, such as Azure SQL Database and Azure Blob Storage.
- Google Analytics: Import data from Google Analytics to analyze website traffic and user behavior directly within Excel.
How to Load Data into Power Query Editor
Now, let’s learn to load data into the Power Query Editor. Here, you have a list of student names and their scores (LINK).
You will be loading data directly from the worksheet, so you need to open the file first and then follow the below steps:
- First, apply an Excel table to the data (Even if you don’t do it Excel will do it for you before loading data into PQ editor).
- Now, select a cell from the table and click on the “From Table/Range” (Data Tab Get & Transform).
- Once you click on the button, Excel confirms the range of data to apply an Excel table to it.
- At this point, you have the data into the power query editor, and it looks something like below.
- Here you can see:
- In the Formula bar, PQ has generated the M code for the table you have just loaded into the editor.
- On the left side of the editor, you have the queries pane where you have the list of the queries.
- On the right side, in the query settings, you have the section called “Applied Steps” where you have all the steps listed. Note: You must be thinking that you haven’t performed any “Changed Type” but there’s a step called “Changed Type” is there. Let me tell you the SMARTNESS of POWER QUERY when you load data into the editor it checks and applies the correct data types for all the columns automatically.
Power Query Examples (Tips and Tricks)
You can learn to perform some of the basic tasks which you normally do with functional formulas in Excel, but with power query, you can do it with a few clicks:
1. Replace Values
You have a list of values, and you want to replace a value or some values with something else. Well, with the help of the power query you can create a query and replaces those values, in no time.
In the below list, you need to replace my name “Puneet” with “Punit”.
- First, edit the list in the power query editor.
- After that, in the power query editor, go to “Transform Tab” and click “Replace Values”.
- Now, in “Value to Find”, enter “Puneet” and in “Replace With” enter “Punit” and after that, click OK.
- Once you click OK all the values get replaced with the new values and now, click on “Close and Load” to load data in the worksheet.
2. Sort Data
Just like normal sorting, you can sort data by using power query and I’m using the same name list which you have used in the above example.
- First, load data in the power query editor.
- In the Home tab, you have two sorting buttons (Ascending and Descending).
- Click on any of these buttons to sort.
3. Remove Columns
Let’s say you got data from somewhere and you need to delete some columns from it. The thing is, you have to delete those columns every time you add new data, right? But, power query can take care of this.
- Select the column or multiple columns that you want to delete.
- Now, right-click and select “Remove”.
Quick Tip: There’s also an option to “Remove Other Columns” where you can delete all the unselected columns.
4. Split Column
Just like the text to column option, you have “Split Column” in power query. Let me tell you how it works.
- Select the column and go to the Home Tab ➜ Transform ➜ Split Column ➜ By Delimiter.
- Select the custom from the drop-down and enter “-” into it.
- Now, here you have three different options to split a column.
- Left-most Delimiter
- Right-most Delimiter
- Each occurrence of the delimiter
If you have only one delimiter in a cell, all three will work in the same way, but if you have more than one delimiter then you have to choose accordingly.
5. Rename a Column
You can simply rename a column by right click and then click on the “Rename”.
Quick Tip: Let say you have a query for renaming a column and someone else rename it by mistake. You can restore that name just with a click.
6. Duplicate Column
In Power Query, there is a simple option to create a duplicate column. All you need to do is right-click on the column for which you want to create a duplicate column and then click on “Duplicate Column”.
7. Transpose Column or Row
In the power query, transposing is a cup of cake. Yes, just one click.
- Once you load data into the power query editor, you just need to select the column(s) or row(s).
- Go to Transform Tab ➜ Table ➜ Transpose.
8. Replace/Remove Errors
Normally for replacing or removing errors in Excel you can use find and replace option or a VBA code. But in power query, it’s a whole lot easier. Look at the below column where you have some errors and you can remove as well as replace them.
When you right-click on the column, you’ll have both of the options.
- Replace Errors
- Remove Errors
9. Change Data Type
You have data in a column but it’s not in the right format. So, every time you need to change its format.
- First, edit data into the power query editor.
- After that, select the column and go to the Transform Tab.
- Now, from data type select the “Date” as a type.
10. Add Column from Examples
In the power query, there is an option to add a sample column which is not actually a sample related to the current column.
Let me give you an example:
Imagine you need day names from a date column. Instead of using a formula or any other option, you can use, you can use the “Add Column from Examples”.
Here’s how to do this:
- Right-click on a column and click on “Add Column from Examples”.
- Here you’ll get a blank column. Click on the first cell of the column to get the list of values you can insert.
- Select “Day of Week Name from Date” and click OK.
Boom! your new column is here.
11. Change Case
You have the following options for changing the case of text in power query.
- Lower Case
- Upper Case
- Capitalize Each Word
You can do it by right click on a column and select any of the above three options. Or, go to the Transform Tab ➜ Text Column ➜ Format.
12. Trim and Clean
To clear data or delete unwanted spaces you can use TRIM and CLEAN options in power query. Steps are simple:
- Right-click on a column or select all the columns if you have multiple columns.
- Go to Transform Tab ➜ Text Column ➜ Format.
- TRIM: To remove trailing and leading whitespaces from a cell.
- CLEAN: To remove non-printable characters from a cell.
13. Add Prefix/Suffix
So you have a list of values and from this list, you want to add a Prefix/Suffix in each cell. In Excel, you can use the concatenate method but in power query, there is a simple to use option for both.
- First, select the column where you need to add Prefix/Suffix.
- Then, go to the Transform Tab ➜ Text Column ➜ Format ➜ Add Prefix/ Add Suffix.
- Once you click on one of the options, you’ll get a dialog box to enter text.
- And after entering the text, click OK.
14. Extract Values
If you are a formula savvy, then I’m sure you agree with me that extracting text or number from a cell requires to combine different functions. But power query has solved a lot of these things in a good way. You have seven ways to extract values from a cell.
15. Only Date or Time
It happens a lot of times that you have date and time, both in a single cell, but needs one of them.
- Select the column where you have the date and time combined.
- If you want:
- Date: Right Click ➜ Transform ➜ Date Only.
- Time: Right Click ➜ Transform ➜ Time Only.
16. Combine Date and Time
Now you know how to separate date and time. But the next you need to know how to combine them.
- First, select the date column and click on the “Date Only” option.
- After that, select both columns (Date and Time) and go to the transform tab and from the “Date and Time Column” Group go to Date and click “Combine Date and Time”.
17. Rounding Numbers
Here are the following options which you have for rounding numbers.
- Round Down: To round down a number.
- Round Up: To round up a number.
- Round: You can choose up to how my decimals you can round.
Here are the steps:
- Select the column and right-click ➜ Transform ➜ Round.
- Round Down: To round down a number.
- Round Up: To round up a number.
- Round: You can choose up to how my decimals you can round.
Note: When you select the “#3 Round” option you need to enter the number of decimals to round.
18. Calculations
There are options that you can use to perform calculations (a lot of). You can find all these options on the Transform Tab (in Number Column group).
- Basic
- Statistics
- Scientific
- Trigonometry
- Rounding
- Information
To perform any of this calculation you need to select the column and then the option.
19. Group by
Let’s say you have a large data set and you want to create a summary table. Here’s what you need to do:
- In the Transform tab, click on the ‘Group by“ button and you’ll get a dialog box.
- Now, from this dialog box select the column with which you want to group and after that, add a name, select the operation, and the column where you have values.
- In the end, click OK.
Note: There are also some advanced options in the “Group by” option which you can use to create a multi-level group table.
20. Remove Negative Values
In one of my blog posts, I have listed seven methods to remove the negative signs and the power query is one of them. Just right click on a column and go to transform option and then click on “Absolute value”.
This instantly removes all the negative signs from the values.
How to Load Data Back to the Worksheet
Once you transform your data, you can load it to the worksheet and use it for further analysis. On the home tab there is a button called “Close and Load” when you click on it you get a drop-down which has options further:
- Close and Load
- Close and Load To
- Once you click on the button, it will show the following options:
- Select how you want to view this data in your worksheet.
- Table
- Pivot Table Report:
- Pivot Chart
- Only Create Connection
- Where do you want to put the Data?
- Existing Worksheet
- New Worksheet.
- Add this data to the Data Model.
- Just select the table option and new worksheet and don’t tick mark the data model and click OK.
- The moment you click OK, it adds a new worksheet with the data.
More Examples to Learn
Auto Refresh a Query
From all the examples that I have mentioned here, this one is the most important. When you create a query, you can make it auto-refresh (you can set a timer). And here are the steps:
- On the Data tab, click on “Queries & Connections” and you’ll get the Queries and Connection pane on the right side of the window.
- Now, right-click on the query and tick mark “Refresh every” and enter the minutes.
How to use a Formula and a Function in Power Query
Just like you can use functions and formulas in Excel worksheet, the power query has its own list of functions that you can use. The basics of function and formulas in power query are the same as Excel’s worksheet functions.
In PQ, you need to add a new custom column to add a function or a formula.
Let’s take an example: In the below data (already in the PQ editor) you have the first name and last name (DOWNLOAD LINK).
Imagine you need to merge both names and create a column for the full name. In this case, you can enter a simple formula to concatenate names from both columns.
- First, go to the Add Column tab and click on the “Custom Column”.
- Now in the custom column dialog box, enter the name of the new column “Full Name” or anything you want to name the new column.
- The custom column formula is the place where you need to enter the formula. So enter the below formula in it:
[First Name]&" "&[Last Name]
- When you enter a formula in the “custom column formula”, PQ verify the formula that you have entered and shows a message “No syntax error have been detected” and if there’s an error it will show an error message based on the type of the error.
- Once you enter the formula and that formula doesn’t have any errors in it, simply press OK.
- Now you have a new column at the end of the data which has values from two columns (first name and the last name).
Use a Function in Power Query
In the same way, you can also use a function while adding a custom column and Power Query has a huge list of functions that you can use. Let’s understand how to use a function with an easy and simple example. I’m continuing the above example where we have added a new column by combining the first name and last name.
But now, you need to convert that full name text which you have in that column into the upper case. The function which you can use is “Text.Upper”. As the name suggests, it converts a text to an upper-case text.
- First, go to the add column tab and click on the custom column.
- Now in the custom column dialog box, enter the column name and below formula in the custom column formula box:
Text.Upper([Full Name])
- And when you click OK it creates a new column with all the names in the uppercase.
- The next thing is to delete the old column and rename the new column. So right-click on the first column and select remove.
- In the end, rename the new column his “Full Name”.
There are a total of 700 functions that you can use in power query while adding a new column and here is the complete list provided by Microsoft for these functions, do check them out.
Edit a Query in PQ
If you want to make some changes in the query which is already in your workbook you can simply edit it and then make those changes. On the Data tab, there’s a button named Queries and Connections.
When you click on this button, it opens a pane on the right side that lists all the queries that you have in the current workbook.
You can right-click on the query name and select edit and you will get it in the power query editor to edit.
When you edit a query, you can see that all the steps which you have performed earlier are listed in the “Applied Steps” that you can also edit or you can perform new steps.
And once you are done with your changes you can simply click on the “Close & Load” button.
Export and Import Connections
If you have a connection which you have used for a query and now you want to share that connection with someone else, you can export that connection as an odc file.
On the query table, there’s a button called “Export Connection” and when you click on it, it allows you to save that query’s connection in your system.
And if you want to import a connection that is shared by someone else, you can simply go to the Data tab and in the Get & Transform click on the existing connections.
And then click on the “Browse for More” button from where you can locate the connection file which has been shared with you and import it to your workbook.
Power Query Language (M Code)
As I mentioned earlier for every step you perform in power query it generates a code (at the backend) which is called M Code. On the Home tab, there is a button called “Advanced Editor” which you can use to see the code.
When you click on the advanced editor it will show you the code editor and that code looks something like the below:
M is a case-sensitive language and like all the other languages it uses variables and expressions. The basic structure of code looks like below where the code starts with the LET expression.
In this code, we have two variables and the values defined to them. In the end, to get the value, IN expression has been used. Now when you click OK it will return the value assigned to the variable “Variablename” in the result.
Check out this resource to learn more about Power Query Language.
You can’t afford to avoid the POWER QUERY. If you think like this, a lot of things which we do with Excel functions or VBA codes can be automated using it, and I’m sure this tutorial inspires you to use it more and more.