Excel Power Query (Table of Contents)
Introduction to Power Query in Excel
Before you start analyzing the data, the step involved is to import the same data. In Excel, we have a generalized way of manually inputting data cell by cell, row by row and column by column. However, it becomes difficult when you have large lines of data to be entered. Consider a dataset that has 100k rows in it. It will be a time-consuming job, right? Even it is not worth to manually enter that much of rows in Excel as it is error-prone. What to do in such cases? Is there any alternative for manual entry? Surely, there is an alternative for the same. Power Query! Nice word to hear? It’s even nicer if you learn how to use it under Excel to import and transform the data under Excel. In this article, we will discuss the Power Query in Excel.
Power Query to Automate the Data Import Process
Power Query is a powerful excel tool that allows a user to import the data from various sources into Excel. These sources include some relational databases such as Microsoft SQL Server, Microsoft Access, Azure, etc. as well as data files such as Excel, Text, CSV, XML, JSON, etc. Power Query can help you in automating the data import task in Excel with minimal efforts and it is so much easier that you can feel like navigating through Excel itself. It can ideally be considered as an ETL tool that allows you to Extract, Transform and Load the data into Excel.
How to Use Power Query in Excel?
Suppose we have a text file in a folder that consists of 1000 rows of data. We will use Power Query to import this file under Excel and try to manipulate the same using Power Query itself.
Step 1: First thing first. We need to create a connection so that we can pull the data from a text file in Excel. Open an Excel file > Click Data tab in Excel ribbon > click Get Data dropdown under the Get & Transform Data section.
Step 2: As soon as you click on Get Data dropdown, you get several options from where you actually can pull the data. We will navigate towards the option named From Folder which is under From File dropdown. This option we are using over From Text/CSV option, because it has more versatility in it than the latter.
Step 3: As soon as you click on the From Folder option, a new window will pop-up. There, you have to browse the path on which the file is located. Click on Browse… button and navigate towards the path where the data file is located.
Click on OK once you are done with the path browsing.
Step 4: A new window will open with a list of all files present at the path browsed with different file attributes such as file name, data type, date modified and path on which the file is located, file extension, etc.
There are 4 more options at the bottom side of this window that reads Combine, Load, Transform Data and Cancel respectively.
Combine allows you to choose between the datasets which you want to combine. However, it doesn’t have a separate Edit option which makes it less versatile as you can’t decide which columns to be combined together.
Load allows you to load the data as a table/Pivot into an Excel sheet irrespective of the actual format of the data under the source file.
Transform Data allows you to transform the source data files. You can add the calculated column, change format for certain columns, add or remove columns, group columns, etc.
Cancel is a button which cancels all other operations under power Query.
Step 5: Click on the Transform Data button and select the file named Source Data.txt, you can see the data layout as shown below:
On the upper ribbon, there are several options such as Home, Transform, Add Column, View. On the left-hand side of the layout, there is a window for Query Setting, we can see all the queries run until here one by one. Please note that each query is a formulated code under Power Query.
Step 6: We now, change the data type of the first column as Date. Select the first column named as Date > Right Click > Change Type navigation bar > Select Date as an option to represent all the number values as dates for the given column.
Step 7: As soon as you click on Date, a new window appears named Change Column Type. Click on Add new step there to change the format of the column as Date.
Have a look at the Date column now, it should have changed the format from whole numbers to the Dates.
Step 8: We can also add a computed/calculated column under this layout before we load the same. Follow the navigation as Add Column > Custom Column under the General section. It will allow you to create a new calculated column based on the custom formula.
Step 9: Change the name of the column as Margin% and formulate it as Margin/Sales under Custom Column as shown below:
Click the OK button and you can see the column being added under the layout.
Step 10: To load this data in Excel, navigate to Home > Close & Load navigation bar > Close & Load To… option (This option allows you to load the data as Table, PivotTable, PivotTable Chart, etc.)
Step 11: Inside Import Data wizard, select the Table layout, Existing Worksheet and click OK.
You can see a data table loaded as shown below:
This is how we can use Power Query to automate the task of data import under Excel. Let’s wrap the things with some points to be remembered.
Things to Remember
- Power Query is the easiest way to automate the task of data import under Excel. It saves manpower, time as well as reduces the human error.
- We can import data from different databases such as SQL Server, Access server, etc. as well as the files such as text, CSV, XML, etc.
Recommended Articles
This is a guide to Power Query in Excel. Here we discuss how to use Power Query in Excel and how to import data from different databases with examples. You can also go through our other suggested articles –
