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.

Practice

1. What does the Table.Transpose function do in Power BI's Power Query?

easy

A. It flips rows and columns in a table.

B. It filters rows based on a condition.

C. It sorts the table by a specific column.

D. It merges two tables into one.

Solution

1. Step 1: Understand the function purpose

   Table.Transpose is designed to switch rows and columns in a table.

2. Step 2: Compare with other options

   Filtering, sorting, and merging are different operations not related to transposing.

3. Final Answer:

   It flips rows and columns in a table. -> Option A

4. Quick Check:

   Transpose = Flip rows and columns [OK]

Hint: Transpose means flip rows and columns [OK]

Common Mistakes:

• Confusing transpose with filtering or sorting
• Thinking transpose merges tables
• Assuming transpose deletes data

2. Which of the following is the correct syntax to transpose a table named SalesData in Power Query?

easy

A. Table.Transpose = SalesData

B. Table.Transpose(SalesData)

C. Transpose.Table(SalesData)

D. SalesData.Transpose()

Solution

1. Step 1: Recall Power Query function syntax

   Functions use the format FunctionName(Parameter). Here, Table.Transpose(SalesData) is correct.

2. Step 2: Identify incorrect syntax

   Assignment with '=', incorrect function/table order, and object method syntax (.Transpose()) are invalid in Power Query M language.

3. Final Answer:

   Table.Transpose(SalesData) -> Option B

4. Quick Check:

   Correct function call = Table.Transpose(SalesData) [OK]

Hint: Use Table.Transpose(tableName) to transpose [OK]

Common Mistakes:

• Using assignment instead of function call
• Swapping function and table names
• Adding parentheses incorrectly

3. Given this table in Power Query:

Product | Jan | Feb
--------|-----|----
A       | 10  | 20
B       | 30  | 40

What will be the first row after applying Table.Transpose?

medium

A. [10, 20, 30]

B. ["Product", "Jan", "Feb"]

C. ["Jan", "Feb", "A"]

D. ["Product", "A", "B"]

Solution

1. Step 1: Understand transpose effect on rows and columns

   Transpose flips rows and columns, so the first row becomes the first column.

2. Step 2: Identify first row after transpose

   Original first column is ["Product", "A", "B"], so this becomes the first row after transpose.

3. Final Answer:

   ["Product", "A", "B"] -> Option D

4. Quick Check:

   Transpose flips rows and columns correctly [OK]

Hint: First row after transpose = original first column [OK]

Common Mistakes:

• Confusing rows with columns after transpose
• Mixing header names with data values
• Assuming transpose sorts data

4. You wrote Table.Transpose(SalesData) but get an error. Which is the most likely cause?

medium

A. SalesData is not a table but a list or other type.

B. You forgot to import the SalesData table.

C. Table.Transpose requires two parameters.

D. Transpose only works on numeric columns.

Solution

1. Step 1: Check input type for Table.Transpose

   Table.Transpose requires a table input. If SalesData is not a table, it causes an error.

2. Step 2: Evaluate other options

   Importing is unrelated to syntax error; function takes one parameter; transpose works on any data type.

3. Final Answer:

   SalesData is not a table but a list or other type. -> Option A

4. Quick Check:

   Input must be a table [OK]

Hint: Ensure input is a table, not a list [OK]

Common Mistakes:

• Passing lists instead of tables
• Assuming transpose needs multiple parameters
• Thinking transpose only works on numbers

5. You have monthly sales data with months as columns and products as rows. You want to analyze sales trends by month vertically. Which approach using transpose is best?

hard

A. Sort the table by product name before transposing.

B. Filter the table to show only one month at a time.

C. Use Table.Transpose to flip months into rows, then add an index for sorting.

D. Merge the sales table with a calendar table without transposing.

Solution

1. Step 1: Understand the data layout and goal

   Months are columns, products are rows. To analyze trends by month vertically, months should become rows.

2. Step 2: Apply transpose and prepare for analysis

   Using Table.Transpose flips months into rows. Adding an index helps keep months in order for trend analysis.

3. Step 3: Evaluate other options

   Filtering one month loses trend view; sorting products doesn't help months; merging without transpose doesn't reshape data as needed.

4. Final Answer:

   Use Table.Transpose to flip months into rows, then add an index for sorting. -> Option C

5. Quick Check:

   Transpose + index = vertical month analysis [OK]

Hint: Transpose months to rows, add index for order [OK]

Common Mistakes:

• Not transposing before analysis
• Filtering instead of reshaping data
• Ignoring the need to keep month order