Forum Discussion
How to create new table that pivots columns based on dynamic values in existing column
I have a table that looks like this:
| Failure | Audience | hits |
| 123456 | Team | 100 |
| 123456 | Insiders | 230 |
| 445632 | Team | 25 |
| 445632 | Insiders | 100 |
| 445632 | Outsiders | 332 |
And I want to create a new table that looks like this:
| Failure | Team | Insiders | Outsiders |
| 123456 | 100 | 230 | |
| 445632 | 25 | 100 | 332 |
How do I do that? I can do it easily enough in a page view via the UI, but cannot figure out how to make a table pivoted on columns this way.
A co-worker helped me figure out the solution.
I used a Summarize operation, and then on the calculated column, did a double filter. The insider FILTER operation matches all rows to the current Failure via an EARLIER operator. The outer FILTER then matches all rows where the "Audience" field equals a specific value. It isn't quite dynamic (I am hard coding to look for specific values in the Audience column, which in my case are a finite set), but it gets the job done.
3 Replies
v-yulgu-msftMicrosoft Employee
Hi WayneRoseberry,
In Query Editor mode, choose the [Audience] column, click "pivot column" option under Transform tab.
Best regards,
Yuliana GuThanks. The data in question is in a table, not a query, so the query editor is not available.
A co-worker helped me figure out the solution.
I used a Summarize operation, and then on the calculated column, did a double filter. The insider FILTER operation matches all rows to the current Failure via an EARLIER operator. The outer FILTER then matches all rows where the "Audience" field equals a specific value. It isn't quite dynamic (I am hard coding to look for specific values in the Audience column, which in my case are a finite set), but it gets the job done.