MS Excel - Power Query Basics for Importing and Transforming Data

Power Query is a data preparation tool available in Microsoft Excel and Microsoft Power BI. It allows users to import data from different sources, clean it, transform it, and load it into a worksheet or data model for analysis. Power Query is especially useful when working with large datasets or when data needs regular updating.

1. What is Power Query?

Power Query is an ETL tool. ETL stands for Extract, Transform, and Load.

  • Extract – Import data from different sources

  • Transform – Clean and modify the data

  • Load – Insert the processed data into Excel or Power BI

It uses a step-by-step approach. Every action you perform is recorded as a step, which can be edited later.

2. Importing Data Using Power Query

Power Query can import data from many sources, such as:

  • Excel files

  • CSV files

  • Text files

  • Databases

  • Web pages

  • Cloud services

In Excel:

  1. Go to the Data tab.

  2. Click Get Data.

  3. Choose the data source.

  4. Preview the data.

  5. Click Transform Data to open the Power Query Editor.

In Power BI:

  1. Click Home tab.

  2. Select Get Data.

  3. Choose your source and load or transform the data.

The preview window shows how the data looks before loading.

3. Understanding the Power Query Editor

The Power Query Editor has three main areas:

  • Data Preview Area – Shows the data table.

  • Queries Pane – Displays all queries created.

  • Applied Steps Pane – Lists all transformation steps.

Each transformation you apply is added to the Applied Steps list. You can modify or delete any step.

4. Common Data Transformation Tasks

Power Query provides many tools to clean and shape data. Some common transformations include:

Changing Data Types
You can convert columns into text, number, date, or other formats to ensure accurate analysis.

Removing Columns
Unnecessary columns can be removed to simplify the dataset.

Filtering Rows
You can filter data based on conditions such as values, dates, or text.

Sorting Data
Sort rows in ascending or descending order.

Renaming Columns
Columns can be renamed for clarity.

Splitting Columns
You can split one column into multiple columns based on a delimiter such as a comma or space.

Merging Columns
Multiple columns can be combined into a single column.

Removing Duplicates
Duplicate records can be removed to maintain data accuracy.

Replacing Values
You can replace incorrect or unwanted values with correct ones.

5. Loading Data

After transforming data:

  • Click Close & Load in Excel to load the data into a worksheet.

  • In Power BI, click Close & Apply to load the data into the model.

The best part is that Power Query remembers all steps. When the original data changes, you can simply click Refresh, and all transformations are applied automatically.

6. Advantages of Power Query

  • Saves time when working with repeated data tasks

  • Reduces manual errors

  • Handles large datasets efficiently

  • Automates cleaning and transformation steps

  • Works with multiple data sources

Conclusion

Power Query is a powerful tool for importing and transforming data efficiently. It allows users to connect to various data sources, clean and shape data using simple tools, and load it for analysis. Because all steps are recorded, the process becomes automated and reusable, making Power Query an essential skill for students and professionals working with data.