Transpose operations in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Transpose a Table Using DAX

You have a table named SalesData with columns Product, Month, and Sales. You want to create a new table that shows Month as rows and Product as columns with the sum of Sales as values.

Which DAX expression correctly transposes the data?

A TRANSPOSE(VALUES(SalesData[Month]))

B SUMMARIZE(SalesData, SalesData[Month], SalesData[Product], "TotalSales", SUM(SalesData[Sales]))

C SUMMARIZECOLUMNS(SalesData[Month], SalesData[Product], "TotalSales", SUM(SalesData[Sales]))

D UNPIVOT(SalesData, SalesData[Month], SalesData[Product], SalesData[Sales])

💡 Hint

Think about a function that groups by multiple columns and aggregates values.

SUMMARIZECOLUMNS groups by Month and Product and sums Sales. This creates a table suitable for pivoting in visuals.

❓ visualization

intermediate

Best Visualization for Transposed Data

You have transposed sales data where months are rows and products are columns showing sales amounts. Which visualization type best displays this transposed data for easy comparison across products and months?

A Pie Chart

B Matrix Visual

C Line Chart

D Stacked Bar Chart

💡 Hint

Look for a visual that supports rows and columns with values in a grid.

The Matrix Visual in Power BI is designed to display data in rows and columns, perfect for transposed tables.

❓ data_modeling

advanced

Handling Transpose with Many-to-Many Relationships

You have two tables: Products and Sales. The Sales table has multiple entries per product per month. You want to create a transposed summary table showing total sales per product per month. What is the best modeling approach to avoid incorrect aggregation?

A Create a one-to-many relationship from <em>Products</em> to <em>Sales</em> on <em>ProductID</em> and use SUMMARIZECOLUMNS with filters.

B Create a relationship between <em>Products[ProductID]</em> and <em>Sales[ProductID]</em> with many-to-many cardinality and use SUMMARIZECOLUMNS.

C Use CROSSJOIN of <em>Products</em> and <em>Sales</em> and then filter by month.

D Duplicate the <em>Sales</em> table and transpose it manually in Power Query.

💡 Hint

Think about correct relationship types for aggregation.

A one-to-many relationship from Products to Sales ensures correct aggregation of sales per product. Many-to-many can cause double counting.

🔧 Formula Fix

advanced

Debugging a Transpose Measure with Incorrect Results

You wrote this DAX measure to transpose sales data by month and product:

TransposedSales = SUMMARIZE(SalesData, SalesData[Month], SalesData[Product], "TotalSales", SUM(SalesData[Sales]))

But the results show duplicated rows and incorrect totals. What is the likely cause?

A Using SUMMARIZE instead of SUMMARIZECOLUMNS causes incorrect context evaluation.

B The measure is missing a FILTER function to limit months.

C The SalesData table is missing a relationship to the Products table.

D The SUM function is aggregating text values instead of numbers.

💡 Hint

Consider differences between SUMMARIZE and SUMMARIZECOLUMNS in context handling.

SUMMARIZE can cause unexpected row duplication due to context transition issues. SUMMARIZECOLUMNS is preferred for reliable aggregation.

🧠 Conceptual

expert

Understanding Transpose Impact on Performance

When transposing large datasets in Power BI, what is the most important factor affecting report performance?

A The use of slicers on transposed data disables query folding.

B The use of calculated columns instead of measures slows down visuals.

C The presence of relationships between tables causes slow refresh.

D The number of columns after transpose increases cardinality and memory usage.

💡 Hint

Think about how data shape affects storage and processing.

Transposing data often increases columns, which raises cardinality and memory use, impacting performance more than relationships or slicers.

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