Removal Operation in Power Query in Power BI

The Removal Operation in Power Query Editor in Power BI allows you to remove unnecessary rows or columns from your dataset. This is a common data-cleaning step to eliminate irrelevant or redundant information, improving dataset quality and reducing report complexity.

---

When to Remove Rows and Columns

Remove Rows:

• Irrelevant Data: Rows that don’t contribute to your analysis (e.g., test data, headers within data).
• Duplicate Records: To avoid overcounting or skewing results.
• Outliers or Errors: Remove rows that have incorrect or extreme values.

Remove Columns:

• Irrelevant Fields: Columns that are not needed for your analysis (e.g., temporary or descriptive fields).
• Sensitive Data: Columns containing private or unnecessary data (e.g., Social Security Numbers).
• Simplifying Data: To reduce the dataset size and focus on relevant fields.

---

Example Table: Sales Data

OrderID	ProductName	CustomerName	Quantity	Price	Discount	Comments
1	Laptop	Alice Johnson	1	1000	50	Delivered
2	Smartphone	Bob Smith	2	500	0	Delivered
3	Tablet	Charlie Brown	1	300	30	Returned
4	Laptop	Diana Prince	3	900	90	Delivered
5	NULL (Test Data)	NULL	NULL	NULL	NULL	NULL

---

Removing Rows

1. Remove Top Rows (e.g., Test Data):

• Scenario: The first row might contain test or placeholder data.
• Steps:

1. Open Power Query Editor.
2. Go to the Home tab and select Remove Rows > Remove Top Rows.
3. Specify the number of rows to remove (e.g., 1).
4. Click OK.

2. Remove Rows Where Comments = “Returned”:

• Scenario: You only want to analyze delivered items.
• Steps:

1. Select the Comments column.
2. Click Home > Remove Rows > Remove Rows Where.
3. Use the filter dropdown or conditional logic: Comments ≠ “Returned”.
4. Apply the filter.

3. Remove Blank Rows:

• Scenario: Remove rows where all columns are empty or contain NULL.
• Steps:

1. Click Home > Remove Rows > Remove Blank Rows.

---

Removing Columns

1. Remove Irrelevant Columns:

• Scenario: Comments column is not required for analysis.
• Steps:

1. Select the Comments column.
2. Click Home > Remove Columns.

2. Remove Multiple Columns:

• Scenario: Both Discount and Comments columns are irrelevant.
• Steps:

1. Select multiple columns (Ctrl + Click).
2. Click Home > Remove Columns.

3. Remove Other Columns:

• Scenario: Keep only OrderID, ProductName, and Price.
• Steps:

1. Select the columns you want to keep.
2. Right-click and choose Remove Other Columns.

---

Final Cleaned Table

OrderID	ProductName	CustomerName	Quantity	Price
1	Laptop	Alice Johnson	1	1000
2	Smartphone	Bob Smith	2	500
3	Tablet	Charlie Brown	1	300
4	Laptop	Diana Prince	3	900

---

Teaching Tips

1. Scenario-Based Examples:

• Use a test dataset where unnecessary rows and columns are apparent.
• Explain why cleaning improves data relevance and analysis speed.

1. Interactive Exercise:

• Ask students to remove columns they think are irrelevant.
• Challenge them to remove rows with specific conditions (e.g., NULL values).

1. Error Handling:

• Demonstrate what happens if irrelevant rows or columns are retained (e.g., skewed results).

By mastering Remove Rows and Remove Columns, students can efficiently clean data for focused and accurate analysis.