Using Excel's Power Query for data transformation

What is Power Query?

In the ever-evolving world of data analytics, staying ahead of the curve is crucial. If you're still grappling with manual data cleaning and preparation in Excel, it's time to embrace a game-changing tool that's been right at your fingertips: Power Query.

Power Query is more than just another Excel feature; it's a robust data transformation and preparation engine that's revolutionizing how professionals handle data. At its core, Power Query offers a user-friendly graphical interface that simplifies the often complex tasks of importing and manipulating data. Gone are the days of writing intricate code or struggling with countless formulas. With Power Query, you can apply sophisticated transformations to your data with just a few clicks.

One of the most compelling aspects of Power Query is its versatility in connecting to data sources. Whether your data resides in databases, text files, websites, or other Excel workbooks, Power Query can seamlessly import it all. This capability alone can save hours of manual data entry and reduce the risk of errors that often creep in during such processes.

How Do You Enable Power Query?

Power Query is available as a free add-in on Excel 2010 and 2013, which you can download from Microsoft's website. The link is available here.

On clicking the Download button, a dialog box opens where you can choose the appropriate download option that suits your OS. Power Query will then be downloaded on your system.

It is a built-in tool starting with Excel 2016 and is available in the Get & Transforms Data Section under Data Tab.?

In the realm of data analytics, Power Query stands out as a game-changer for both Excel and Power BI users. Its Extract, Transform, and Load (ETL) capabilities revolutionize data handling. Let's break down the four crucial phases that make Power Query an indispensable tool for data professionals.

The Four Phases of Power Query

Power Query allows users to extract, transform, and load (ETL) data from various sources into Excel or Power BI. The four phases of Power Query are:

Phase 1: Connect

The journey begins with connecting to your data sources. Power Query shines in its versatility here:

• Access a wide array of data sources: databases, spreadsheets, web pages, and more
• Set up secure connections with proper authentication and authorization
• Lay the foundation for your data analysis by establishing robust data pipelines

This phase is all about accessing your data securely and efficiently, setting the stage for the transformations to come.

Importing data is easy with the help of the Get & Transform Data section of the Data tab in Excel.?

You can import data from several different sources.

• From Files: Excel files(Workbook), Text or CSV files, XML files, and JSON files.
• From Databases: SQL Server, Microsoft Access, SQL Server Analysis Services.
• From Other Sources: Excel Tables/ Ranges, Web, Microsoft Query, OData feeds.

Phase 2: Transform

Once your data is loaded, the transformation phase begins. This is where Power Query truly flexes its muscles:

• Clean messy data with ease
• Remove duplicates to ensure data integrity
• Reshape datasets to fit your analytical needs
• Split or combine columns for better data structure
• Apply complex transformations without altering source data

The Transform phase empowers you to mold your data into the perfect shape for analysis, all within a user-friendly interface.

The six main sections of the Power Query Editor are as follows:

• Query Editor Ribbon: ?This ribbon is similar to the one on the Excel interface. Various commands are organized in separate tabs.??
• Query List: This section lets you browse through a list of all queries in your current workbook.
• Formula Bar: The current transformation’s formula will be specified here in the M language.
• Data Preview: You can see the preview of your data based on the current transformation step. You can access various transformation commands by right-clicking on the column header or by clicking on the respective column header's filter option.
• Properties: This section consists of a list of query steps. Here, you will be able to name your query. Naming a query is an important step to identify a query easily.
• Applied steps: Each transformation step you take will be recorded here in chronological order. You can add, remove, edit, or reorder the steps if required.

Phase 3: Combine

In our data-rich world, insights often come from multiple sources. The Combine phase is crucial for creating a unified view:

• Merge tables from different sources
• Append datasets to create comprehensive views
• Join data using common keys for relational analysis
• Break down data silos by integrating diverse data sources

This phase allows you to create a holistic view of your data, enabling deeper insights and more comprehensive analysis.

Power Query has two different options that help us combine different datasets. The two options are:

• Append?
• Merge

APPEND

In Power Query, the append operation creates a new table by joining all the rows from the first query, followed by all rows from the second query. Follow the steps below to understand how to perform an Append operation.

Step 1:

• ?Firstly, we have to load the data into the Excel workbook. In this demonstration, you will learn how to Append data from a CSV file.
• This can be done by selecting the Data tab, followed by the ‘Text/CSV File’ command.?
• Once we have selected the option, an Import Data dialog box opens. Select the desired CSV file and click on import.
• ?A dialog box opens, which shows a preview of the data contained. Clicking on ‘Load’ will enter the data in a new sheet.?
• Continue this step to add the required data into new sheets.

Step 2: Now, to append the data available on different sheets, we can navigate to the Data Tab. Here we can find an option called Get data, clicking on which will open up a drop-down menu. You will find an option called Combine Queries. On selecting it, you will find the ‘Append’ option.

Step 3: Clicking on ‘Append’ will open up a window with different options where we can choose to append two tables or more than three. Next, we have to select the sheets that have to be appended. When done, we can click on OK.

Step 4: The Power Query editor opens up, and the data has now been appended. We can click on ‘Close and Load’ to save these changes, which loads the updated data to an Excel spreadsheet.

MERGE

The Merge option is similar to the JOIN function in SQL. Merge is a way of combining two existing queries and creating a new query.?

Step 1:

• Firstly, we have to load the data into the Excel workbook. In this example, we will demonstrate how to Merge data from a CSV file.
• This can be done by clicking on the Data tab, followed by the ‘Text/CSV File’ command.?
• Once we have selected the option, an Import Data dialog box opens.?
• Select the desired CSV file and click on import.?
• A dialog box opens, which shows a preview of the data contained. Clicking on ‘Load’ will enter the data in a new sheet.?
• Continue this step to add all the required datasets to be merged into different sheets.

Step 2: Now, to Merge the data available in different sheets, we have to navigate to the Data tab. Here we can find an option called Get Data. On clicking it, a drop-down menu will be displayed, which has the option to Combine Queries. On selecting this, click on ‘Merge’.

Step 3: A window will be displayed where we can select the sheets that we want to merge. Now, choose the two columns by clicking on the column header based on which we want to connect both the sheets. Then, click on OK.

Step 4: Once that is done, the Power Query editor opens up with a new column in the end that holds the merge result. To save the changes made, we click on “Close and Load”.

Step 5: The merged data is now loaded onto our Excel worksheet.?

This was all about combining the queries using Merge and Append operations.?

Phase 4: Load

The final step is deciding where your transformed and combined data will reside:

• Load directly into Excel worksheets for immediate analysis
• Prepare data for stunning Power BI visualizations
• Create dynamic connections that refresh automatically
• Ensure your analyses always use the most up-to-date information

The Load phase bridges the gap between data preparation and analysis, setting you up for success in your data-driven decision making.

Conclusion:

As we've explored throughout this article, Power Query stands as a transformative tool in the world of data analysis. Its ability to streamline the extract, transform, and load (ETL) process has revolutionized how professionals handle data in both Excel and Power BI environments.