Unpivoting columns in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to analyze monthly sales data by product category. The data is currently in a wide format with months as columns. You need to transform it into a long format for better analysis.

📊 Data: You have a table showing sales amounts for three product categories (Electronics, Clothing, Home Goods) across three months (January, February, March). Each month is a separate column.

🎯 Deliverable: Create a Power BI query that unpivots the monthly sales columns into two columns: 'Month' and 'Sales'. Then build a simple bar chart showing total sales by month.

Sample Data

Product Category	January	February	March
Electronics	12000	15000	13000
Clothing	8000	7000	9000
Home Goods	5000	6000	5500

1

Step 1: Load the sales data table into Power BI Desktop using 'Get Data'.

Use 'Excel' or 'CSV' depending on your file format and load the table.

Expected Result

The sales data table appears in Power BI's Power Query Editor.

2

Step 2: Select the columns for the months (January, February, March) to unpivot.

In Power Query Editor, select columns 'January', 'February', and 'March'.

Expected Result

The three month columns are highlighted for transformation.

3

Step 3: Unpivot the selected month columns to transform them into attribute-value pairs.

Right-click on the selected columns and choose 'Unpivot Columns'.

Expected Result

The table now has columns: 'Product Category', 'Attribute', and 'Value'. 'Attribute' contains month names, 'Value' contains sales amounts.

4

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

Double-click column headers and rename accordingly.

Expected Result

Columns are named 'Product Category', 'Month', and 'Sales'.

5

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

Click 'Close & Apply' in Power Query Editor.

Expected Result

The transformed data is loaded into Power BI's data model.

6

Step 6: Create a bar chart visual to show total sales by month.

In the report view, add a bar chart visual. Drag 'Month' to Axis and 'Sales' to Values (set aggregation to SUM).

Expected Result

A bar chart displays total sales for January, February, and March.

Final Result

Sales by Month Bar Chart

January  | ██████████████ 25000
February | ██████████████████ 28000
March    | ████████████████ 27500

(Product categories combined)

✓February had the highest total sales across all product categories.

✓January had the lowest total sales.

✓Unpivoting the data made it easy to compare sales by month.

Bonus Challenge

Create a slicer to filter the bar chart by 'Product Category' so you can see monthly sales for each category separately.

Show Hint

Add a slicer visual, drag 'Product Category' to it, and interact with the bar chart.