More Examples of Power Query Usage in Excel

Power Query is a powerful tool for data transformation and automation. Below are more practical examples of how you can use Power Query in your daily workflows:

---

1. Combine Data from Multiple Files

Scenario: You have monthly sales data saved in separate Excel files and want to combine them into a single table.

Steps:

1. Go to Data > Get Data > From File > From Folder.
2. Select the folder containing the Excel files.
3. Power Query will display the list of files. Click Combine > Combine & Load.
4. Power Query will merge all files into a single table.
5. Load the data back into Excel.

Example Output: All monthly sales files (e.g., January, February) are combined into one table.

---

2. Remove Duplicates from a Dataset

Scenario: You have a list of customer records and want to remove duplicate entries.

Steps:

1. Load your data into Power Query (Data > Get Data > From Table/Range).
2. In Power Query Editor, select the columns to check for duplicates.
3. Click Remove Duplicates in the Home tab.
4. Load the cleaned data back to Excel.

Example Output: Original Table:

Customer ID	Name	Email
101	Alice	alice@example.com
102	Bob	bob@example.com
101	Alice	alice@example.com

Cleaned Table:

Customer ID	Name	Email
101	Alice	alice@example.com
102	Bob	bob@example.com

---

3. Split Columns Based on Delimiters

Scenario: You have a single column with full names (e.g., “John Smith”) and want to split them into First Name and Last Name.

Steps:

1. Load the data into Power Query.
2. Select the column to split.
3. Click Split Column > By Delimiter and choose the space character (” “).
4. Rename the resulting columns as “First Name” and “Last Name”.
5. Load the transformed data back into Excel.

Example Output: Original Table:

Full Name
John Smith
Jane Doe

Transformed Table:

First Name	Last Name
John	Smith
Jane	Doe

---

4. Pivot and Unpivot Data

Scenario: You have sales data in a pivot table format and want to unpivot it for analysis.

Steps:

1. Load your data into Power Query.
2. Select the columns to unpivot (e.g., “January”, “February”).
3. Click Transform > Unpivot Columns.
4. Rename the resulting columns (e.g., “Month”, “Sales”).
5. Load the unpivoted data back to Excel.

Example Output: Original Table:

Product	January	February
Apple	5000	6000
Banana	4000	4500

Unpivoted Table:

Product	Month	Sales
Apple	January	5000
Banana	January	4000
Apple	February	6000
Banana	February	4500

---

5. Create Custom Columns

Scenario: Add a calculated column that categorizes sales as “High” or “Low” based on a threshold.

Steps:

1. Load your data into Power Query.
2. Click Add Column > Conditional Column.
3. Set conditions, e.g., “If Sales > 5000, then High, else Low”.
4. Load the updated table back to Excel.

Example Output: Original Table:

Product	Sales
Apple	6000
Banana	4000

Transformed Table:

Product	Sales	Category
Apple	6000	High
Banana	4000	Low

---

6. Replace Values

Scenario: You want to replace incorrect or missing values (e.g., “N/A”) with actual data.

Steps:

1. Load your data into Power Query.
2. Select the column with the incorrect values.
3. Click Transform > Replace Values and specify the old and new values.
4. Load the corrected data back to Excel.

Example Output: Original Table:

Product	Sales
Apple	5000
Banana	N/A

Corrected Table:

Product	Sales
Apple	5000
Banana	0

---

7. Merge Queries

Scenario: Combine two tables based on a common column (e.g., “Product ID”).

Steps:

1. Load both tables into Power Query.
2. Click Home > Merge Queries.
3. Choose the common column (e.g., “Product ID”) to join the tables.
4. Select the type of join (e.g., Inner Join).
5. Expand the merged data and load it back to Excel.

Example Output: Sales Table:

Product ID	Sales
P001	5000
P002	3000

Product Details Table:

Product ID	Product Name
P001	Apple
P002	Banana

Merged Table:

Product ID	Sales	Product Name
P001	5000	Apple
P002	3000	Banana

---