How to create new table that pivots columns based on dynamic values in existing column

[WayneRoseberry]

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.

[WayneRoseberry · Accepted solution]

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.

[v-yulgu-msft]

Hi @WayneRoseberry,

In Query Editor mode, choose the [Audience] column, click "pivot column" option under Transform tab.

Best regards,
Yuliana Gu

[WayneRoseberry]

Thanks. The data in question is in a table, not a query, so the query editor is not available.