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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Microsoft Employee
Microsoft 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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