Power Query in Excel – Shiksha Online

Power Query in Excel – Shiksha Online

5 mins read535 Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Mar 3, 2023 13:34 IST

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.

2022_06_Power-Query-in-Excel-1.jpg

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.

Content

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.

Most Popular and Powerful Formulas in Excel
Most Popular and Powerful Formulas in Excel
Excel has various functions ready to process your information to get the desired results. Familiarize yourself with each of them. Once you master various functions, you can create advanced MS...read more
How To Use Slicer In Excel – Shiksha Online
How To Use Slicer In Excel – Shiksha Online
The article discusses the use of Slicer in Excel to sort and filter huge datasets
How to Create MIS Report in Excel?
How to Create MIS Report in Excel?
A Management Information System (MIS) report in Excel is a vital tool for businesses and organizations, offering valuable insights by consolidating and presenting data in an organized and comprehensible format....read more

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.

Tutorial – XLOOKUP in Excel – Shiksha Online
Tutorial – XLOOKUP in Excel – Shiksha Online
The article talks about an advanced and flexible lookup function, XLOOKUP.
How to Import Text Files to Excel
How to Import Text Files to Excel
Managing massive volumes of data is tricky, especially when those are on your text files. Moving data to Excel for better calculations and presentations is an efficient way to handle...read more
How To Calculate Percentage In Excel
How To Calculate Percentage In Excel
Percentages are a very powerful tool for data analysis. They can be used to make data more concise, easier to understand, and more comparable. In this tutorial, we will learn...read more

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.
How to Use Excel Filter Shortcut?
How to Use Excel Filter Shortcut?
Excel Filter shortcuts are keyboard combinations that you can use to apply filters to your data quickly and easily. The article will explain different ways to use Excel Filter shortcuts,...read more
How to Divide in Excel?
How to Divide in Excel?
Division is one of the four basic arithmetic operations, and it is a common operation performed in Excel. To divide in Excel, you can use the forward slash (/) operator....read more
How to Multiply in Excel?
How to Multiply in Excel?
Learn how to multiply in Excel using different criteria. Understand through different examples.

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

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio