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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
KrisIsLearning
Frequent Visitor

Make column for each unique value of a specified column to sum them later on via grouping

Hello,

 

I'm looking for an optimized way to do what I accomplished (yes I already solved it but I'm searching for an easier/better way).

 

Step 1: I'm starting with some sample data:

 

IDState
1Wait
1Wait
1Breaking
2Breaking
2Breaking
2Check
2Wait
2Breaking
2Wait

 

Step 2: What I'm trying to accomplish is (currently I'm handling this with adding each time a conditional column):

 

 

IDWaitBreakingCheck
1100
1100
1010
2010
2010
2001
2100
2010
2100

 

Ultimately I'm grouping on ID and summing up the columns Wait, Breaking and Check per ID

 

I find Step 2 somewhat cumbersome as it requires adding (potentially) quite some conditional columns. Is there a built in functionality like pivot/unpivot that I can try to make this process faster and less error prone?

 

 

Kind regards, Kris

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

Hi @KrisIsLearning 

 

If the goal is to end up with this

m_dekorte_0-1684149698937.png

 

Group By first and then Pivot on "State" summing "Count", here are the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpPzCxRitXBwnEqSk3MzsxLBwsYESXgnJGanA3nwU3DqhYiGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, State = _t]),
    GroupRows = Table.Group(Source, {"ID", "State"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    PivotCol = Table.Pivot(GroupRows, List.Distinct(GroupRows[State]), "State", "Count", List.Sum)
in
    PivotCol

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

View solution in original post

2 REPLIES 2
KrisIsLearning
Frequent Visitor

That's a great solution. Thanks for that.

m_dekorte
Super User
Super User

Hi @KrisIsLearning 

 

If the goal is to end up with this

m_dekorte_0-1684149698937.png

 

Group By first and then Pivot on "State" summing "Count", here are the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpPzCxRitXBwnEqSk3MzsxLBwsYESXgnJGanA3nwU3DqhYiGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, State = _t]),
    GroupRows = Table.Group(Source, {"ID", "State"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    PivotCol = Table.Pivot(GroupRows, List.Distinct(GroupRows[State]), "State", "Count", List.Sum)
in
    PivotCol

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors