Excel Power Query: Exploring Data Transformation Magic

Imagine you have a magical data transformation tool that can be your personal time-saver for all things related to data. You know those moments when you find yourself dealing with repetitive tasks like copying, pasting, and column mixing? Well, Power Query jumps in and says, ‘Hey, let me handle that for you!’

So, let’s explore the power of Power Query.

What is Excel Power Query?

Power Query is an Excel tool mostly used by data analysts, accountants, and business specialists to get data from multiple sources, manipulate, combine, and manage their data for further analysis.

Installation Guide

To install the Power Query in different versions of Excel follow this guide:

Excel 2016, 2019, Office 365

Power Query is already installed in excel 2016, 2019 and office 365.

Excel 2007

Power Query is not in 2007 version of Excel.

Excel 2010 and 2013

Power Query in Excel 2010 and 2013 we need to install add-in separately from the official site of Microsoft. After downloading add-in follow these steps to install.

• From the File tab select Option followed by Add-in.
• Select COM Add-ins and click GO.

• Choose Microsoft Power Query for Excel and click OK.

Phases of Power Query

By using power query, we can get, transform, and combine data from different sources to make it suitable for analysis and reporting. The four stages of using Power Query are as follows.

1.  Get Data

Power Query works with different data sources, including databases, text files, Excel workbooks and others. During this process, we can connect to these databases to extract data. During this phase, we can also define any essential verification or permission-related information. Anyone can work with data from any source, of any size and shape, using Power Query.

We can import data as follows:

• Click on the Data Tab, and navigate to Get Data in the Get and Transform section.
• Select the data source and open the file.

2.   Transform Data

Once data has been collected in Power Query, we can clean and manipulate data using a transformation tool. It provides a user-friendly interface for applying a wide range of data transformations to shape and structure your data according to your analysis needs. It includes a wide range of features, such as removing columns, changing data type, filtering rows, grouping by, and appending tables.

Steps to Transform Data:

Step 1: While selecting data, opt for the Transform choice.

Step 2: Power Query Editor will open in a separate window.

Exploring Power Query Editor

Before we begin with data transformation, let’s take a look at the editor interface.

• Query Editor Ribbon: Almost the same as Excel having different options in separate tabs.
• Query List: It is a collection of individual queries that have been created and organized in the power query editor.
• Formula Bar: The area where we write advanced formula expressions using M language.
• Data Preview: Allow us to view our data before applying transformations or loading to the destination.
• Properties: In this section, we will find a collection of individual query steps. We can also provide a unique name for future access.
• Applied Steps: It is an organized record of transactions we have applied to our data.

This was all about the power query editor.

Step 3: Now that we have the data in the editor, let’s make some transformation to it.

Sorting: We can arrange the data in either ascending or descending order by using the filter button followed by the dropdown menu.

Filter Data: Next, we will narrow down the data by selecting only the entries related to males. To do this, simply uncheck the “female” option from the dropdown and press OK.

Step 4: Now you can see the M-Code in the formula bar showing the operations that have been performed

Step 5: The changes we have made will appear in the Applied Steps section.

3.   Combine Data

The combining data phase in Power Query is where we merge data from different sources into a single table. This is helpful when we want to compare data from different sources or create a single view of our data.

There are two main methods for combining queries: merging and appending.

Merging combines two or more tables based on a common column.

• From the Home tab in Power Query Editor click on the Merge Queries button. A dialog box will appear.
• Select the common column from both queries.
• Choose the type of join you want: inner, left, right, or full outer and click OK.

• The data is merged, but it is not yet visible in the table.

• We need to expand the merged column to display its contents as separate columns in your dataset as follows.

• Here is the final merged data table.

Appending adds the data from one table to the end of another table.

• Open an excel file having multiple sheets and select sheets from the navigator window and click on Transform Data.

• Now the data is in the editor.
• Click on Append from the Combine section of Home Tab.
• Select Tabel from the Append window and click OK.

• And the data is appended.

4.   Load Data

After transforming and combining data the next step is to load data on a worksheet or power bi. We can check the data preview before loading to make sure it is correct. The output can appear as a table, pivot chart, or pivot table.

There are two options that how we want to upload data:

Close and Load: Allow us to directly load data to a new worksheet in excel.

Close and Load To: It gives us flexibility that where we want to load the transformed data. A new window will appear to choose the destination.

When we click OK, the data will be added to a new worksheet.

Let’s select the Table option then New worksheet and press OK.

Automating Query Updates

We can enable auto query refresh by following these steps:

1. Open the Queries & Connections pane from the Data tab.

2. Now right-click on the query, select Properties, check the Refresh every option, and input the desired interval in minutes.

Utilizing Functions in Power Query

Power Query has its own functions and formulas. The basics are almost the same as an Excel worksheet. Let’s explore how we can add a column using Power Query.

For example, we want to add a new column Salary based on their ages. We will do this by following these steps:

1. From the Add Column tab click on Custom Column.
2. Specify the column name in the New Column Name section.
3. Enter the formula in the Custom Column formula field and click Ok.

Modifying a Query in Power Query Editor

Editing power query in the editor is not a complex task. Let’s see how we can do that:

• Open Queries & Connection from Data tab.
• Right-click on the query then edit.

• Now we can see Applied Steps that can be edited and can also perform a new one.

Moving and Sharing Connections

If we want to share a connection for any query, we can export the connection as an ODC file. By clicking the Export Connection button on the Query table.

To import a connection, follow these steps:

• Open the Data tab.
• Select Existing Connections from the Get & Transform Data section.
• Browse for more and open the desired connection.

The M-Code

The code behind each applied step in the power query is M Code. Like other languages, M is also a case-sensitive language. It includes variables, logic, expressions, and additional elements.

To view the M code, open the Advanced Editor from the Home tab of the Query Editor.

Conclusion

In summary with the help of Power Query in Excel we can organize, clean, manage, and share our results with others effectively. The most important is that it records all the steps and queries that we can use in the future and can save a lot of time. In this way, it automates the task and we just need to load the data.