Question

You are transforming a support ticket table in Power Query. It has columns TicketID, Priority (High/Medium/Low), and AgentName. You want to reshape the data so each Priority value becomes its own column showing the count of tickets per agent, with AgentName as the row identifier. When you select the Priority column and choose Pivot Column, using TicketID as the Values column, the operation returns errors in the new columns. What should you do to produce the correct counts?

Accepted Answer

In the Pivot Column dialog, expand Advanced options and set the Aggregate Value Function to Count (Not Blank) instead of the default that tries to sum a text column.

Answer

Change the TicketID column data type to Text before pivoting so the values can be concatenated into the new columns.

Answer

Unpivot the Priority column first, then apply Pivot Column with the default Don't Aggregate option.

Answer

Remove the AgentName column so the pivot has a unique key, then re-add it with a merge afterward.