Power Query in Excel – Shiksha Online

Power Query is a pretty powerful tool that allows you to import and clean millions of rows into a data model. Learn how to use Power Query in Excel through a detailed example.

Power Query, also known as the Get & Transform in Excel, is a popular business intelligence function. It helps to import data from various source files and sort them into a spreadsheet to be easy to use. The best part about Power Query is that it doesn’t require the user to learn any specific code and gives faster results. You can set up a query and reuse it by just refreshing it.

Power Query was first made available in MS Excel in 2010. The feature came as a free add-in in Excel 2010 and 2013. Now since Excel 2016, it comes wholly integrated. It can be an excellent tool for those working with massive data sets like accountants, data scientists, data analysts, and data processors.

Explore MS Excel Tutorials

Content

• How to use Power Query in Excel?
• Example – Using Power Query in Excel
• Points to Remember

How to Use Power Query in Excel?

Excel’s Power Query is used to search data sources, make connections to data sources, and then shape the data according to our analysis requirements. Once we configure the data to our needs, we can share our findings and create various reports using more queries.

There are four steps involved in using Power Query in Excel, which include –

Import

First, we import the data, which can be somewhere, in the cloud, in service, or locally.

Transform

The second step would be to change the data’s shape per the user’s requirements.

Combine

In this step, we perform some transformation and aggregation steps and combine data from multiple sources to produce an integrated report.

Load

This merges and adds columns in one query with matching columns in other queries in the workbook.

Example – Using Power Query in Excel 

We have two .txt files on students’ performance, and we need to merge those files in Excel.

As we can see from the image below, we have two types of files in the folder, but we want to get the data from only text files into the Excel file.

We will copy this data into Excel using the text-to-column option 

Must Explore – MS Excel Courses

Step 1

• First, we need to get the data into Power Query to make the necessary changes to the data to import it into an Excel file.

To do it, we will follow the below path –

Data ⇒ Get & Transform ⇒ New Query ⇒ From File ⇒ From Folder

Step 2

• Select the folder location.

• Click ‘OK’

Step 3

A dialog box will open containing the list of all files in the selected folder with column headers like Content, Name, Extension, Date Accessed, Date Modified, Date created, Attributes, and Folder path.

You will see three options, i.e., Combine, Load, and Edit data.

Combine: This option is used to go to a screen where we can choose which data to combine. This step does not involve any editing and doesn’t give us control over which files to merge.

Load: This option will simply load the table, as shown in the image above, into the Excel spreadsheet instead of the actual data in the files.

Edit: Unlike the ‘Merge’ command, if we use this command, we can choose which files to combine, i.e., we can combine only one type of file (same extension).

As in our case, we want to combine only text files (.txt); we will choose the “Edit” command.

We can see “Applied Steps” on the right side of the window. For now, there is only one step: grab the details of the files in the folder.

Step 4

There is a column named ‘Extension’ where we can see that the values ​​of the column are written in both cases, i.e., uppercase and lowercase.

• However, we need to convert all values ​​to lowercase since the filter differentiates between the two. To do the same, we need to select the column and choose “Lowercase” from the “Format” command menu.

Step 5

• Filter the data using the ‘Extension’ column for text files.

Step 6

We need to combine data for both text files now using the first column, ‘Content.’

• Click on the icon to the right of the column name.

Step 7

A dialog subtitled ‘Combine Files’ will open where we have to select the delimiter as ‘Tab’ for text files (files with extension ‘.txt’) and you can choose the basis for data type detection.

• Click ‘OK’.

• After clicking ‘OK,’ we will get the combined data from the text files in the ‘Power Query’ window.

We can see the steps applied to the data using a power query on the right side of the window.

• After making all the necessary changes to the data, we can load the data into an Excel spreadsheet. This involves using the ‘Close and Load In’ command under the ‘Close’ group on the ‘Home’ tab.

• We must choose whether we want to load the data as a table or a connection. Then click ‘Load.’

• Now we can see the data as a table in the worksheet. ‘Workbook Queries’ panel will appear on the right side, which we can use to edit, duplicate, merge, add the queries, etc.

Points to Remember

• Power Query does not change the source data. Instead of changing the source data, it records every step the user takes in connecting or transforming the data. Once the user completes the data setup, it takes the refined dataset and brings it into the workbook.
• Power Query is case-sensitive.
• While consolidating the files in the specified folder, we must use the ‘Extension’ column. It is essential to exclude temporary files (with the extension ‘.tmp’ and names with the ‘~’ sign). Power Query may also import these files.

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel