Transpose operations in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to see the monthly sales data transposed so that months become rows and product categories become columns for easier comparison.

📊 Data: You have a table with columns: Product Category, January Sales, February Sales, March Sales.

🎯 Deliverable: Create a Power BI report that shows the sales data transposed: months as rows and product categories as columns, with sales values filled in.

Sample Data

Product Category	January Sales	February Sales	March Sales
Electronics	1000	1200	1100
Clothing	800	950	900
Home Goods	600	700	650

1

Step 1: Load the sales data table into Power BI.

Use 'Get Data' to import the table with columns: Product Category, January Sales, February Sales, March Sales.

Expected Result

The sales data table is loaded into Power BI with three product categories and their monthly sales.

2

Step 2: Unpivot the monthly sales columns to transform months into rows.

In Power Query Editor, select columns 'January Sales', 'February Sales', 'March Sales', then right-click and choose 'Unpivot Columns'.

Expected Result

The table now has columns: Product Category, Attribute (month), Value (sales). Each month is a row per product category.

3

Step 3: Rename the 'Attribute' column to 'Month' and 'Value' column to 'Sales'.

In Power Query Editor, double-click 'Attribute' and rename to 'Month'. Rename 'Value' to 'Sales'.

Expected Result

Columns are now: Product Category, Month, Sales.

4

Step 4: Close and apply the changes to load the transformed data into Power BI.

Click 'Close & Apply' in Power Query Editor.

Expected Result

The transformed data with months as rows is loaded into Power BI.

5

Step 5: Create a matrix visual to display months as rows and product categories as columns with sales values.

Add a Matrix visual. Drag 'Month' to Rows, 'Product Category' to Columns, and 'Sales' to Values (set aggregation to SUM).

Expected Result

Matrix shows months in rows, product categories in columns, and sales numbers filled in the cells.

Final Result

Matrix Visual:

          | Electronics | Clothing | Home Goods
---------------------------------------------
January   |     1000    |   800    |    600
February  |     1200    |   950    |    700
March     |     1100    |   900    |    650

✓Electronics had the highest sales in February.

✓Clothing sales increased steadily from January to February, then slightly decreased in March.

✓Home Goods sales were lowest among categories but showed a small increase in February.

Bonus Challenge

Create a line chart showing sales trends over the three months for each product category using the transposed data.

Show Hint

Use the transformed table with 'Month' as the axis, 'Sales' as values, and 'Product Category' as legend in the line chart visual.