What Is Power Query In Excel?
Power Query in Excel is an add-in introduced by Microsoft in 2013. Power Query is an additional tool that allows users to do various ETL processes. ETL means Extracting the data from multiple sources, Transforming the extracted data to fit in needs of the business reports, and finally Loading the data to either Power BI or Excel.
Power Query is available for both Excel and Power BI. Using Power Query in Excel, we can clean and transform the data, add conditional columns, apply filters to the raw data and get only the required data. After doing the essentials to the data, we can then finally load the data to source systems like Power BI and Excel.
For instance, assume we are bringing the sales data from SQL, which has the data for the last 10 years. However, we need only the last 3 years’ data to analyze, so using a power query, we can apply the date filter as on or after the date and then mention the starting date of the data.
Table of contents
- What Is Power Query?
- Power Query in excel is used to perform the ETL process. Extract, Transform, and Load the data.
- It can perform VLOOKUP kind of operations by using the merge option. We can merge multiple tables into one.
- This tool can split the column based on the common delimiter and may ignore the first row as header. Hence, the user needs to promote the first row as a header in the subsequent steps.
- Apart from performing ETL, users can also pivot and unpivot columns using power query in excel.
How Do You Enable Power Query?
In Excel 2010 and 2013 Version: Power Query is available from Excel 2013 onwards. However, in the 2010 and 2013 versions of Excel, we need to download the power query from the Microsoft website.
You can click on the following link to download the power query from the Microsoft website.
When downloading the power query in excel, we need to choose the download version, either 32-bit or 64-bit, as per our Excel bit version.
To enable power query in excel, we should install it on our computer.
Now we should be able to see the Power Query as a separate tab in Excel.
In Excel 2016, 2019, and Office 365 Versions: In excel versions, Excel 2016, 2019, or Office 365, power query is available under the Data tab in Get and Transform Data group.
In this group, we have various data sources to insert the data. Then, based on the data source, we can choose any of the above options and launch a power query to perform the ETL process.
Likewise, we can enable power query in excel.
Power Query Window
Below is the window of the power query editor.
- Ribbon: It is just like our MS Excel ribbons. Under each ribbon, we have several features to work with.
- List of Queries: These are the tables imported to excel in this workbook.
- Formula Bar: It is like our formula bar in excel but here it is M Code.
- Data Preview: Here, we can see the data preview of the selected query table.
- Properties: It is the properties of the selected table.
- Applied Steps: This is an important feature. All the applied steps in the power query are displayed here. We can undo actions by deleting the queries.
What Can You Do With Power Query?
Power Query in excel is an ETL tool for performing various data cleaning and manipulation activities. For example, power Query can do the following activities to assist in data transformation.
- Remove all the unwanted columns.
- Keep duplicate values or remove duplicates.
- Merge two tables into one.
- Append two worksheets or tables of data into one.
- Fill down values from the above cells dynamically.
- Create index columns at various incremental rates.
- Remove error rows.
- Aggregate or pivot the data into summarized rows, thus minimizing the data’s number of rows.
- Transpose of the data.
- Replace values.
- Split the column into first and last names.
- Consolidate multiple workbooks into one.
Likewise, Excel has various kinds of transformation and data clean-up activities.
Let us look at the following examples to understand power query in excel.
Example #1: Perform VLOOKUP In Power Query
Using power query in excel, we can perform the VLOOKUP operation in the power query by using the merge queries option.
For instance, let us take the following two tables in Excel.
We have a Sales Table and a Manager Table.
Now the objective is to bring add the manager column to the Sales Table by fetching the value from the Manager Table based on the common column between two tables, i.e., the Region column.
The steps used to perform VLOOKUP in power query are as follows:
Step 1: Convert both the tables into Excel Table format. Select any of the cells in the sales table and enter the shortcut keys Ctrl + T. The Create Table window appears.
Step 2: Click OK, and the above data will be converted to Excel Table format.
Step 3: Similarly, convert the manager table to Excel Table by performing the above similar steps.
Step 4: Select the sales table. Under the Table Design tab, give the name as Sales_Table.
Step 5: Similarly, give the name for the manager table as well.
Step 6: Select any of the cells in the Sales_Table. Under the Data tab, click on From Table/Range.
Step 7: The power query editor window opens up. Under the Home tab, choose Close & Load To by clicking on the drop-down list of Close and Load option.
Step 8: The Import Data window pops up. Choose Only Create Connection options.
Step 9: Click on OK. Now we can see the new window, Queries & Connections, on the right side of the Excel.
Step 10: Now select the Manager_Table and perform the same steps to create the connection for this table.
Now we can see two connections in the Queries & Connections window.
Step 11: Now, we need to merge these two connections again in the power query.
So, go to the Data tab under the Get Data options and choose Combine Queries.
Then, choose Merge.
The Merge window pops up, as shown in the following image.
Step 12: Choose Sales_Table in the first drop-down box.
Step 13: Similarly, choose Manager_Table in the second drop-down box.
Step 14: Now, choose the common column between the two tables, i.e., Region column in both the tables.
Step 15: In the Join Kind drop-down, choose Left Outer (all from first, matching from second). We can see all the records from the first table (Sales_Table) and matching records from the second table (Manager_Table).
Step 16: Click OK. Once again, the power query editor window appears.
Now, we can see one additional column in the Sales_Table, i.e., Manager_Table.
Step 17: Under the Manager_Table column, click on the double-pointed arrow key.
Step 18: Now, we can see all the columns from the Manager_Table.
Since we only need Manager column, choose only this column.
Step 19: Click OK.
We can see the manager column.
Step 20: Under the Home tab, click on the Close and Load drop-down and choose the Close & Load option.
Step 21: Now, we can see that a new worksheet has been added to the worksheet in the name of Merge1.
This worksheet also shows a newly added column called the Manager column in the Sales_Table.
One of the advantages of using power query in excel is whenever the change happens in the Manager_Table; it will dynamically update the results.
For instance, let us change the West manager’s name from Peter to Jamie in the Manager_Table column.
Now in the Queries & Connections window, click on the Refresh icon in the Merge1 query.
Now, queries will automatically update the manager’s name as Jamie.
Example #2: Split Columns Based On Delimiter & Use First Row As Header
Let us use the same data from example #1 but have the data in text or notepad file as shown in the image below:
In the above data structure, each column is segregated by a comma. We will try to split columns using power query.
Step 1: Open an Excel file. Under the Data tab, click on From Text/CSV option.
Step 2: Now, excel will ask us to choose the file from the desired folder.
Step 3: Click on Import. Now, we will see the preview of the data.
Next, click on Transform Data.
Step 4: The power query editor window appears, as shown in the image below.
Data is in the desired format. So, let us split the columns.
Step 5: Under the Home tab, we have an option called Split Column. Click on the drop-down list and choose By Delimiter.
Step 6: In the Split Column by Delimiter window, we need to choose the delimiter separating each column.
In this case, ‘Comma’ is the delimiter separating each column. So, choose a comma as the delimiter.
Step 7: Under the Split at section, choose the option Each occurrence of the delimiter
since we need to separate the column whenever there is a comma, which is the delimiter in our example.
Step 8: Now click on OK. We can see that the data is split into columns.
However, the problem here is that power query in excel did not recognize the data headers.
Therefore, we need to do another transformation here. So, we should promote the first row of the data as Headers.
Under the Home tab, click on Use First Row as Headers.
When we click on this option, the data will have the first row as headers.
Example #3: Unpivot Data
In some cases, the summarized data may appear like the below table.
The summarized table shows the monthly sales of each customer. However, for analysis purposes, we need the data to be in tabular format, i.e., each customer data should be in a separate row.
Let us unpivot these columns in the power query.
Step 1: Load the data to the power query.
Step 2: Select only the columns we want to unpivot, i.e., select all the month columns.
Step 3: Go to the Transform tab and click on Unpivot Columns.
Step 4: As soon as we click on unpivot columns, we can see the transformed data, as shown in the following image.
Step 5: Click on Close and Load under the Home tab.
Now the unpivoted data gets loaded to the Excel worksheet.
Important Things To Note
- Power Query in excel is an add-in available for the 2010 and 2013 versions. However, from Excel 2016 version onwards, we have a power query embedded in Get and Transform group under the Data tab.
- It always converts the Excel data into Excel Table format.
- The tool uses M code for its formulas making formulas in Power Query in excel different than Excel formulas.
- Power Query M code is case-sensitive and can perform various activities like unpivot columns, insert Index columns, etc.,
Frequently Asked Questions
For instance, we have the following monthly sales data in an Excel spreadsheet.
Assume we need to insert the serial number column. Then, we can perform the index column operation in the power query.
Go to Add Column. Under the Index Column, choose From 1.
We will create an index column, as c in the image below.
Power Query can assist data transformation by doing the following activities.
● Transpose of the data.
● Unpivot columns.
● Insert Index columns.
● Remove duplicate values.
● Split columns.
● Filter out the data.
● Merge and Append tables.
Excel formulas are available in Power Query in excel but with different function type prefixes. For instance, in Excel, we have a function called TRIM, and in power, we have TEXT.TRIM function because TRIM is a text function.
Similarly, we have the ROUND function in Excel, and in power query, we have NUMBER.ROUND because ROUND is a numerical function.
Power Query is an add-on for Excel 2010 and 2013 versions. First, we need to download it from Microsoft website and install it. Once we install it, we should see a separate tab called Power Query.
However, in Excel 2016, 2019, and Office 365 versions, we have this as a built-in tool under the Data tab in Get and Transfer group.
This article must be helpful to understand the Power Query in excel, with its formula and examples. You can download the template here to use it instantly.
This has been a guide to Power Query in excel. Here we discuss how to use power query with examples and downloadable excel template. You can learn more form the following articles –