Power Query in Data Analysis

Power Query is useful in data analysis when we need to import, clean, and reshape data before analyzing it. It helps us perform repeated data preparation tasks in a few clicks and save them as steps, so the same cleaning can be applied again whenever the data changes. In Excel, Power Query is used to handle data from workbooks, CSV files, text files, folders, databases, and websites.

In this article, we will learn about Power Query in data analysis, its uses, and the main features used to import and transform data in Excel.

What Is Power Query in Data Analysis?

Power Query is a data connection and data preparation tool in Excel. It is also known as Get & Transform Data. It allows us to connect to different data sources, apply transformations such as removing columns, changing data types, and splitting text, and then load the result into a worksheet or the Data Model.

Every action we perform is recorded as a step in a list called Applied Steps. When the source data changes, we only need to refresh the query, and Excel repeats all the steps automatically. We do not need to clean the data again by hand.

For example, if we receive a monthly sales file with extra spaces, wrong data types, and blank rows, we can clean it once using Power Query. Next month, we can paste the new file and click Refresh to get the same clean result.

Power Query vs Formulas

Sample Dataset Used in This Article

We will use the following raw dataset in the examples. The headings are in row 1, and the data is in the range A1:E7. It is stored in an Excel Table named RawSales.

Notice that the customer names have different cases, one name has an extra space at the end, and one row is blank.

Where to Find Power Query in Excel

Power Query is built into Excel 2016 and later, and into Microsoft 365. In Excel 2010 and 2013, it is available as a free add-in.

• Go to the Data tab.
• Use the Get Data button, or the From Table/Range, From Text/CSV, and From Web buttons in the Get & Transform Data group.

Supported Data Sources

Parts of the Power Query Editor

How to Import Data Using Power Query

Importing from an Excel Table or Range

Steps

1. Click on any cell inside the data.
2. Go to the Data tab and click on From Table/Range.
3. If asked, check that the range is correct and that My table has headers is selected, and click OK.
4. The Power Query Editor opens with the data.

Importing from a CSV or Text File

Steps

1. Go to Data > Get Data > From File > From Text/CSV.
2. Select the file and click Import.
3. Check the preview, and click Transform Data to open the editor.

Explanation:
In the above steps, we select a data source and open it in the Power Query Editor. After that, Excel shows a preview of the data. Finally, we can clean the data in the editor before loading it into the worksheet.

Understanding Applied Steps

Every transformation is saved as a step in the Applied Steps list on the right side of the editor. We can click on any step to see how the data looked at that point. We can rename a step, change its order, or delete it by clicking the X beside it.

Explanation:
Applied steps work like a recipe. When the query is refreshed, Excel runs the steps from the top to the bottom. This is why the same cleaning is repeated automatically on new data.

Common Data Cleaning Transformations

Remove Blank Rows

Steps

1. Go to Home > Remove Rows > Remove Blank Rows.

Output:
The empty row between Order ID 103 and 105 is removed.

Explanation:
In the above example, we use the Remove Blank Rows option. After that, Power Query finds the rows in which every column is empty. Finally, it removes them from the data.

Remove and Choose Columns

Steps

1. Select the columns we do not need.
2. Right-click on the heading and choose Remove Columns.

To keep only the required columns, use Home > Choose Columns and select the columns to keep. It is safer than removing, because new unwanted columns in future data are ignored.

Change Data Type

Power Query detects data types automatically, but we should check them. The icon on the left of each column heading shows the type.

Steps

1. Click on the icon on the left of the column heading.
2. Select the correct type, such as Whole Number, Decimal Number, Date, or Text.

Output:
The Order Date column is converted to the Date type, and the Sales column is converted to Whole Number.

Explanation:
In the above example, we change the data type of two columns. After that, Power Query treats the dates and numbers as real dates and numbers. Finally, we can sort, filter, and calculate with them correctly.

Trim and Clean Text

Steps

1. Select the Customer column.
2. Go to Transform > Format > Trim to remove the extra spaces.
3. Go to Transform > Format > Clean to remove non-printable characters.

Explanation:
In the above example, we use Trim and Clean on the Customer column. After that, Power Query removes the extra space after Karan Mehta and any hidden characters. Finally, the text becomes consistent and ready for matching.

Change Text Case

Steps

1. Select the Customer column.
2. Go to Transform > Format > Capitalize Each Word.

Output:

Explanation:
In the above example, we use Capitalize Each Word. After that, Power Query changes the first letter of each word to uppercase and the rest to lowercase. Finally, all the names follow the same format.

Replace Values

Steps

1. Select the column.
2. Go to Transform > Replace Values.
3. Enter the value to find and the value to replace it with, and click OK.

This is useful for correcting spellings, such as replacing Nrth with North.

Remove Duplicates and Handle Errors

To remove duplicate rows, select the column or columns and choose Home > Remove Rows > Remove Duplicates. To remove rows with errors, choose Remove Errors. To replace errors with a value, choose Replace Errors.

Fill Down

The Fill Down option fills empty cells with the value above them. It is useful when a category name is written only once for a group of rows.

Steps

1. Select the column with blank cells.
2. Go to Transform > Fill > Down.

Splitting and Combining Columns

Split Column

Steps

1. Select the column, such as Customer.
2. Go to Home > Split Column > By Delimiter.
3. Choose Space as the delimiter, and select Left-most delimiter.
4. Click OK.

Output:

Explanation:
In the above example, we split the Customer column using a space. After that, Power Query separates the first name and the last name. Finally, it creates two columns, which we can rename as First Name and Last Name.

Merge Columns

Steps

1. Hold the Ctrl key and select the columns to combine.
2. Go to Transform > Merge Columns.
3. Choose a separator, enter the new column name, and click OK.

Adding New Columns

Column from Examples

This option lets us type the result we want, and Power Query finds the pattern.

Steps

1. Go to Add Column > Column From Examples.
2. Type the expected result in the first one or two rows.
3. Press Enter when the suggestions are correct, and click OK.

Custom Column

A custom column uses a formula in the M language.

Steps

1. Go to Add Column > Custom Column.
2. Enter a column name, such as Tax.
3. Enter the formula [Sales] * 0.18.
4. Click OK.

Output:
For Order ID 101, the Tax column shows 14400.

Explanation:
In the above example, we create a custom column using the Sales column. After that, Power Query multiplies each sales value by 0.18. Finally, the new Tax column is added to the data.

Conditional Column

Steps

1. Go to Add Column > Conditional Column.
2. Enter a name, such as Category.
3. Set the rule: if Sales is greater than or equal to 50000, then output High, else output Low.
4. Click OK.

Output:

Explanation:
In the above example, we use a Conditional Column to classify each sale. After that, Power Query checks the rule for every row. Finally, it adds the text High or Low in the new column.

Filtering and Sorting Rows

We can click the drop-down arrow on a column heading to sort the data, to filter by value, or to use Text Filters, Number Filters, and Date Filters. For example, we can keep only the rows where Region is North, or where Sales is greater than 40000.

Explanation:
Filtering in Power Query works differently from filtering in a worksheet. The rows that do not match are removed from the query output and are not just hidden. The original source stays unchanged.

Group By

The Group By option summarizes data by a category, similar to a pivot table.

Steps

1. Go to Home > Group By.
2. Select Region as the grouping column.
3. Enter a new column name, such as Total Sales.
4. Select the operation Sum and the column Sales, and click OK.

Output:

Explanation:
In the above example, we group the data by Region. After that, Power Query adds the sales of each region. Finally, it returns one row for each region with the total sales.

Merge Queries

Merge Queries combines two tables based on a matching column. It works like VLOOKUP or a SQL join. Suppose we have a second table named Managers with the columns Region and Manager.

Steps

1. Go to Home > Merge Queries.
2. Select the first table, RawSales, and click on the Region column.
3. Select the second table, Managers, and click on the Region column.
4. Choose a Join Kind, such as Left Outer, and click OK.
5. Click on the expand icon in the new column and select Manager.

Output:
Each sales row now shows the manager of its region. For example, Order ID 101 shows Ravi.

Explanation:
In the above example, we merge the two tables using the Region column. After that, Power Query matches each row of the first table with the second table. Finally, it adds the Manager column to the sales data.

Join Kinds

Append Queries

Append Queries stacks the rows of one table below another. It is useful when the same type of data is stored in different tables, such as monthly sales sheets.

Steps

1. Go to Home > Append Queries.
2. Choose two tables or three or more tables.
3. Select the tables, and click OK.

Explanation:
In the above steps, we select the tables to combine. After that, Power Query places the rows of the second table below the first. Finally, columns with the same heading are aligned, so the result is one long table. The column names must match for the data to line up correctly.

Combining Files from a Folder

If a folder contains many files with the same structure, such as monthly sales files, we can combine them in one query.

Steps

1. Go to Data > Get Data > From File > From Folder.
2. Select the folder, and click Open.
3. Click on Combine > Combine & Transform Data.
4. Select the sample file and the sheet, and click OK.

Explanation:
In the above steps, we point Power Query to a folder. After that, it reads all the files and combines them. Finally, when a new file is added to the folder, a refresh adds its data automatically.

Unpivot Columns

Unpivoting converts data from a wide format to a long format. Many reports show months in separate columns, but analysis tools work better when the months are in rows.

Before:

Steps

1. Select the Region column.
2. Go to Transform > Unpivot Columns > Unpivot Other Columns.
3. Rename the new columns to Month and Sales.

After:

Explanation:
In the above example, we select the Region column and unpivot the other columns. After that, Power Query turns the month headings into values of a new column. Finally, the data becomes ready for pivot tables and charts. The Pivot Column option does the reverse.

Basics of the M Language

Every step in Power Query is written in a formula language called M. We usually do not need to write it, but knowing the basics helps us edit and fix queries. To see the full code, go to Home > Advanced Editor.

Example

let

Source = Excel.CurrentWorkbook(){[Name="RawSales"]}[Content],

RemovedBlanks = Table.SelectRows(Source, each [Order ID] <> null),

Cleaned = Table.TransformColumns(RemovedBlanks, {{"Customer", Text.Proper}})

in

Cleaned

l

Explanation:
In the above code, the let section lists the steps one by one. After that, each step uses the result of the previous step. Finally, the in section tells Power Query which step to return. Note that M is case-sensitive, so Text.Proper must be written exactly as shown.

Loading Data into Excel

After cleaning, we need to load the data.

Steps

1. Go to Home > Close & Load.
2. To choose where the data goes, click the arrow and select Close & Load To.
3. Choose Table, PivotTable Report, Only Create Connection, or Add this data to the Data Model.
4. Click OK.