How to Use Power Query in Excel?
Excel Power Query is used for searching data sources, making connections with data sources, and then shaping the data according to our analyzing requirement. Once we have done with shaping the data as per our needs, we can also share our findings and create various reports using more queries.
Fundamentally, there are 4 steps and order of these 4 steps in Power Query is as follows:
- Connect: We first connect to the data, which can be somewhere, in the cloud, in service, or locally.
- Transform: The Second step would be to change the shape of the data as per user requirement.
- Combine: In this step, we perform some transformation and aggregation steps and combine data from both sources to produce a combined report.
- Manage: This merges and appends columns in a query with matching columns in other queries in the workbook.
There are many super-powerful features of Excel Power Query.
Suppose, we have purchase data for the last 15 years in 180 files. Now the management of an organization would require consolidating the numbers before analyzing them. Management could take any one of the following methods:
- They would open all files and copy-paste them onto one file.
- On the other hand, they can use a wise solution, which is to apply formulas, as it is prone to error.
Whatever method they choose it contains a lot of manual work and after a few months, there would be new sales data for the added duration. They will have to do the same exercise again.
However, Power Query can help them not to do this tedious and repetitive work. Let us understand this excel power query with example.
Suppose, we have text files in a folder with sales data and we want to get that data in our excel file.
As we can see in the below image that we have two types of files in the folder but we want to get the data of only text files in the excel file.
To do the same, steps would be:
Step 1: First, we need to get the data in the Power Query so that we can make required changes in the data to import that in excel file.
To do the same, we will choose “From Folder” option from “From File” menu after clicking on command “Get Data” from “Get & Transform” group in “Data” tab.
Step 2: Select the location of the folder by browsing.
Click on ‘OK’
Step 3: A dialog box will open containing the list for all files in the selected folder with the column headers as ‘Content’, ‘Name’, ‘Extension’, ‘Date accessed’, ‘Date modified’, ‘Date created’, ‘Attributes’ and ‘Folder Path’.
There are 3 options i.e. Combine, Load, and Transform Data.
- Combine: This option is used to go to a screen where we can choose which data to combine. Edit step is skipped for this option and gives us no control over which files to combine. Combine function takes every file in the folder to consolidate which can be lead to errors.
- Load: This option will just load the table as displayed above in the picture into Excel worksheet instead of the actual data in the files.
- Transform Data: Unlike ‘Combine’ command, if we use this command then we can choose which files to combine i.e. we can combine only one type of files (same extension).
As in our case, we want to combine only text files (.txt); we will choose the “Transform Data” command.
We can see “Applied Steps” on the right side of the window. For now, there is only a single step done that is to take files details from the folder.
Step 4: There is a column named ‘Extension’ where we can see that values in the column are written in both cases i.e. upper case and lower case.
However, we need to convert all the values to lower case as filter differentiates between both. To do the same, we need to select the column and then choose “Lower Case” from the “Format” command’s menu.
Step 5: We will 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’. We will click on the icon placed at the right side of the column name.
Step 7: A dialog box captioned as ‘Combine Files’ will open where we need to select the delimiter as ‘Tab’ for text files (files with ‘.txt’ extension’) and can choose the base for data type detection. And click on ‘OK’
After clicking on ‘OK’, we will get the combined data of text files in the ‘Power Query’ window.
We can change the data type of the columns as required. For the ‘Revenue’ column, we will change the data type to ‘Currency’.
We can see the steps applied to the data using a power query on the right side of the window.
After making all the required changes in the data, we can load the data into excel worksheet using ‘Close & Load To’ command under the ‘Close’ group in the ‘Home’ tab.
We need to choose whether we want to load the data as Table or Connection. Then click on ‘OK’.
Now we can see the data as a table in the worksheet
And ‘Workbook Queries’ pane on the right side which we can use for editing, duplicating, merging, appending the queries and for many other purposes.
Excel Power Query is very much useful as we can see that 601,612 rows have been loaded within a few minutes.
Things to Remember
- Power Query does not change the original source data. Instead of changing original source data, it records each step that is taken by the user while connecting or transforming the data, and once the user completes shaping the data, it takes refined data set and brings it into the workbook.
- Power Query is case-sensitive.
- While consolidating the files in the specified folder, we need to make sure that using ‘Extension’ column, we must exclude temporary files (having the extension ‘.tmp’ and name of these file starts with ‘~’ sign) as Power Query can import these files also.
This has been a guide to a Power Query in Excel. Here we learn how to use power query to manage our data in excel with the help of step by step examples. You can learn more about excel from the following articles –