Lesson 7.1 – Power Query: Data Import & Transformation

Lesson Overview

Power Query is a data connectivity and preparation tool in Excel that allows users to import, clean, transform, and combine data from multiple sources without altering the original data. It is essential for efficient data analysis and automation.

1. Introduction to Power Query

• Located under Data → Get & Transform Data

• Allows importing from Excel, CSV, databases, web, and other sources

• All transformations are recorded as steps, making the process repeatable

2. Importing Data

2.1 Steps to Import Data

1. Go to Data → Get Data → From File → From Excel/CSV

2. Select the file → Click Import

3. Data loads into Power Query Editor for transformation

2.2 Example

• Import monthly sales data from multiple CSV files into a single Excel workbook

3. Cleaning & Transforming Data

3.1 Common Transformations

• Remove Columns/Rows: Delete unnecessary data

• Change Data Type: Ensure columns have correct formats (e.g., Date, Text, Number)

• Trim & Clean: Remove extra spaces and non-printable characters

• Replace Values: Correct inconsistent entries (e.g., “NY” → “New York”)

• Split Columns: Split full names into first and last names

3.2 Example

• Clean a product list by removing blank rows, trimming spaces, and standardizing categories

4. Merging & Appending Data

4.1 Append Queries

• Combine data from multiple tables or files into one

• Example: Combine January, February, March sales data into a single table

4.2 Merge Queries

• Join tables based on a common column (like VLOOKUP but dynamic)

• Example: Merge sales data with product info to get product names and prices

5. Loading Transformed Data

• After transformation, click Close & Load

• Data can be loaded as:

  • Table in Excel sheet

  • Pivot Table

  • Connection only (for further analysis)

6. Best Practices

1. Keep original files intact; Power Query does not alter source files

2. Name each query clearly for easy identification

3. Apply consistent transformations for repeatable results

4. Use Refresh All to update imported data after source changes

7. Real-Life Examples

• Finance: Consolidate expense reports from multiple departments

• Sales: Combine regional sales CSV files into one report

• HR: Clean employee data from multiple sheets for analysis

• Operations: Merge inventory lists from different warehouses

8. Practice Questions

1. Import a CSV file into Power Query and load it as a table.

2. Remove blank rows and trim extra spaces in a dataset.

3. Replace inconsistent entries like “NY” with “New York.”

4. Append three months of sales data into a single query.

5. Merge two tables: sales data with product details based on Product ID.