Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WayneRoseberry
New Member

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

I have a table that looks like this:

 

FailureAudiencehits
123456Team100
123456Insiders230
445632Team25
445632Insiders100
445632Outsiders332

 

And I want to create a new table that looks like this:

 

FailureTeamInsidersOutsiders
123456100230 
44563225100332

 

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.

1 ACCEPTED SOLUTION
WayneRoseberry
New Member

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.

View solution in original post

3 REPLIES 3
WayneRoseberry
New Member

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
Employee
Employee

Hi @WayneRoseberry,

 

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

1.PNG

2.PNG

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.