Introduction to Power Query in Excel

Power Query is a powerful data transformation and query tool in Excel that simplifies importing, cleaning, and transforming data. It’s especially useful for combining and reshaping data from multiple sources.

---

Key Features of Power Query

• Import Data: From various sources such as Excel files, databases, and online services.
• Transform Data: Clean, reshape, and filter data without modifying the source.
• Automate Workflows: Save queries for reuse, reducing repetitive tasks.

---

Examples to Understand Power Query

Example 1: Importing and Cleaning Data

Suppose you have the following data with inconsistent formatting:

Name	Age	Department
John Doe	29	Sales
jane SMITH	32	MARKETING
MICHAEL B.	27	HR

Steps in Power Query

1. Go to Data > Get & Transform Data > Get Data.
2. Select From Table/Range (ensure the data is formatted as a table).
3. Power Query Editor opens:
   • Use Transform > Capitalize Each Word to fix inconsistent names.
   • Change the Department column to proper case.
   • Filter out rows with missing values using Remove Rows > Remove Blank Rows.
4. Click Close & Load to save the cleaned data back to Excel.

---

Example 2: Combining Data from Multiple Tables

Suppose you have two tables:
Table 1: Sales Data

Product ID	Sales
P001	5000
P002	3000

Table 2: Product Details

Product ID	Product Name
P001	Apple
P002	Banana

Steps in Power Query

1. Import both tables:
   • Go to Data > Get Data > From Other Sources > Blank Query.
   • Load both tables into Power Query.
2. Merge Tables:
   • In Power Query Editor, go to Home > Merge Queries.
   • Choose a common column (Product ID) to join the tables.
   • Select Join Kind: Left Outer Join.
3. Expand the merged table:
   • Click the expand icon next to the new column and select Product Name.
4. Click Close & Load to view the combined table in Excel.

---

Example 3: Creating a Dynamic Monthly Report

Imagine you receive a new file every month with updated sales data. Instead of manually updating your workbook:

1. Import the folder containing all monthly files:
   • Go to Data > Get Data > From File > From Folder.
   • Browse to the folder location and select it.
2. Transform Data:
   • Power Query will load all files in the folder.
   • Use Combine Files to merge them into a single table.
   • Clean and format the data as needed.
3. Save and Automate:
   • Click Close & Load.
   • In the future, simply add new files to the folder, and the query will refresh automatically.

---