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]