Column operations (rename, remove, reorder) in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a clean sales data table with only relevant columns, properly named and ordered for easy reporting.

📊 Data: You have a sales data table with columns: OrderID, CustomerName, ProductCode, ProductName, QuantitySold, SaleDate, SalesRep, Region, and DiscountApplied.

🎯 Deliverable: Prepare the sales data table by renaming columns for clarity, removing unnecessary columns, and reordering columns to match the reporting needs.

Sample Data

OrderID	CustomerName	ProductCode	ProductName	QuantitySold	SaleDate	SalesRep	Region	DiscountApplied
1001	John Smith	P100	Notebook	5	2024-05-01	Alice	East	Yes
1002	Mary Johnson	P200	Pen	10	2024-05-02	Bob	West	No
1003	James Lee	P300	Stapler	3	2024-05-03	Charlie	North	Yes
1004	Patricia Brown	P100	Notebook	7	2024-05-04	Alice	East	No
1005	Michael Davis	P200	Pen	15	2024-05-05	Bob	West	Yes
1006	Linda Wilson	P400	Marker	8	2024-05-06	Charlie	South	No
1007	David Martinez	P300	Stapler	4	2024-05-07	Alice	East	Yes
1008	Barbara Garcia	P100	Notebook	6	2024-05-08	Bob	West	No

1

Step 1: Rename columns for clarity in Power Query Editor.

Rename 'CustomerName' to 'Customer', 'ProductCode' to 'SKU', 'QuantitySold' to 'Quantity', 'SaleDate' to 'Date', 'SalesRep' to 'Sales Representative', 'DiscountApplied' to 'Discount'.

Expected Result

Columns renamed to: OrderID, Customer, SKU, ProductName, Quantity, Date, Sales Representative, Region, Discount.

2

Step 2: Remove unnecessary columns that are not needed for reporting.

Remove the 'SKU' column from the table.

Expected Result

Table columns now: OrderID, Customer, ProductName, Quantity, Date, Sales Representative, Region, Discount.

3

Step 3: Reorder columns to match reporting needs: Date, OrderID, Customer, ProductName, Quantity, Discount, Sales Representative, Region.

Drag and drop columns in Power Query Editor or use 'Choose Columns' to reorder as specified.

Expected Result

Columns reordered to: Date, OrderID, Customer, ProductName, Quantity, Discount, Sales Representative, Region.

4

Step 4: Apply changes and load the cleaned data into Power BI for reporting.

Click 'Close & Apply' in Power Query Editor.

Expected Result

Cleaned and reordered sales data table ready for use in reports.

Final Result

---------------------------------------------
| Date       | OrderID | Customer       | ProductName | Quantity | Discount | Sales Representative | Region |
|------------|---------|----------------|-------------|----------|----------|----------------------|--------|
| 2024-05-01 | 1001    | John Smith     | Notebook    | 5        | Yes      | Alice                | East   |
| 2024-05-02 | 1002    | Mary Johnson   | Pen         | 10       | No       | Bob                  | West   |
| 2024-05-03 | 1003    | James Lee      | Stapler     | 3        | Yes      | Charlie              | North  |
| 2024-05-04 | 1004    | Patricia Brown | Notebook    | 7        | No       | Alice                | East   |
| 2024-05-05 | 1005    | Michael Davis  | Pen         | 15       | Yes      | Bob                  | West   |
| 2024-05-06 | 1006    | Linda Wilson   | Marker      | 8        | No       | Charlie              | South  |
| 2024-05-07 | 1007    | David Martinez | Stapler     | 4        | Yes      | Alice                | East   |
| 2024-05-08 | 1008    | Barbara Garcia | Notebook    | 6        | No       | Bob                  | West   |
---------------------------------------------

✓The cleaned table has clear column names that are easy to understand.

✓Unnecessary columns like SKU were removed to simplify the data.

✓Columns are ordered logically to support reporting and analysis.

Bonus Challenge

Create a new calculated column in Power Query that combines 'Customer' and 'Region' into a single column called 'Customer Region'.

Show Hint

Use the 'Add Column' feature and the formula: [Customer] & " - " & [Region]

Practice

1.

What is the main purpose of renaming columns in Power BI's Power Query Editor?

easy

A. To give columns clear, meaningful names for easier understanding

B. To delete unwanted data from the table

C. To change the data type of the column

D. To create new calculated columns

Solution

1. Step 1: Understand column renaming

   Renaming columns means changing their names to something more descriptive or meaningful.

2. Step 2: Purpose of renaming

   This helps users and report viewers understand what data the column holds without confusion.

3. Final Answer:

   To give columns clear, meaningful names for easier understanding -> Option A

4. Quick Check:

   Renaming improves clarity = To give columns clear, meaningful names for easier understanding [OK]

Hint: Rename columns to clear names before loading data [OK]

Common Mistakes:

• Confusing renaming with deleting columns
• Thinking renaming changes data values
• Mixing renaming with changing data types

2.

Which of the following is the correct way to remove a column named SalesAmount in Power Query Editor?

easy

A. Rename the column to Remove

B. Double-click the SalesAmount column and type Delete

C. Drag the SalesAmount column to the end of the table

D. Right-click the SalesAmount column header and select Remove

Solution

1. Step 1: Identify how to remove columns in Power Query

   Removing a column is done by right-clicking its header and choosing the remove option.

2. Step 2: Check each option

   Only Right-click the SalesAmount column header and select Remove correctly describes this action. Double-clicking edits names, renaming doesn't remove, and dragging reorders columns.

3. Final Answer:

   Right-click the SalesAmount column header and select Remove -> Option D

4. Quick Check:

   Remove column = Right-click + Remove [OK]

Hint: Right-click column header to remove it quickly [OK]

Common Mistakes:

• Trying to delete by renaming
• Assuming double-click deletes column
• Dragging column removes it

3.

Given a table with columns Product, Sales, and Date, what will be the new column order after applying this step in Power Query Editor?

Table.ReorderColumns(Source, {"Date", "Product", "Sales"})

medium

A. ["Sales", "Date", "Product"]

B. ["Product", "Sales", "Date"]

C. ["Date", "Product", "Sales"]

D. ["Sales", "Product", "Date"]

Solution

1. Step 1: Understand Table.ReorderColumns function

   This function changes the order of columns to the list provided in the second argument.

2. Step 2: Apply the given list

   The list is ["Date", "Product", "Sales"], so the columns will be reordered exactly in that sequence.

3. Final Answer:

   ["Date", "Product", "Sales"] -> Option C

4. Quick Check:

   ReorderColumns list = new order [OK]

Hint: ReorderColumns sets columns exactly as listed [OK]

Common Mistakes:

• Assuming original order stays
• Mixing up column names order
• Ignoring the reorder list

4.

You tried to rename a column in Power Query Editor but got an error. Which of these is the most likely cause?

Table.RenameColumns(Source, {"Sales", "Revenue"})

medium

A. The source table must be empty before renaming

B. The rename list should be a list of lists like {{"Sales", "Revenue"}}

C. The new name must be numeric

D. You cannot rename columns in Power Query Editor

Solution

1. Step 1: Check syntax for Table.RenameColumns

   The function expects a list of pairs (old name, new name) inside another list, like {{"Old", "New"}}.

2. Step 2: Identify error cause

   The code uses a single pair without the outer list, causing a syntax error.

3. Final Answer:

   The rename list should be a list of lists like {{"Sales", "Revenue"}} -> Option B

4. Quick Check:

   RenameColumns needs list of lists [OK]

Hint: Use double braces {{old, new}} to rename columns [OK]

Common Mistakes:

• Using single list instead of list of lists
• Thinking renaming is not allowed
• Expecting numeric names only

5.

You have a table with columns ID, Name, Age, and Salary. You want to remove Age, rename Salary to Income, and reorder columns so Name comes first. Which sequence of Power Query steps is correct?

hard

A. Remove Age, Rename Salary to Income, Reorder columns to ["Name", "ID", "Income"]

B. Rename Salary to Income, Remove Age, Reorder columns to ["ID", "Name", "Income"]

C. Reorder columns to ["ID", "Name", "Salary", "Age"], Remove Age, Rename Salary to Income

D. Remove Name, Rename Age to Income, Reorder columns to ["ID", "Salary"]

Solution

1. Step 1: Remove the Age column

   First, remove the unwanted Age column to exclude it from the table.

2. Step 2: Rename Salary to Income

   Next, rename the Salary column to Income for clarity.

3. Step 3: Reorder columns with Name first

   Finally, reorder columns so Name is first, followed by ID and Income.

4. Final Answer:

   Remove Age, Rename Salary to Income, Reorder columns to ["Name", "ID", "Income"] -> Option A

5. Quick Check:

   Remove, Rename, Reorder in that order [OK]

Hint: Remove unwanted, rename needed, reorder last [OK]

Common Mistakes:

• Reordering before removing columns
• Renaming wrong columns
• Removing needed columns by mistake