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

[KrisIsLearning]

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:

ID	State
1	Wait
1	Wait
1	Breaking
2	Breaking
2	Breaking
2	Check
2	Wait
2	Breaking
2	Wait

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

ID	Wait	Breaking	Check
1	1	0	0
1	1	0	0
1	0	1	0
2	0	1	0
2	0	1	0
2	0	0	1
2	1	0	0
2	0	1	0
2	1	0	0

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

[KrisIsLearning]

That's a great solution. Thanks for that.

[m_dekorte · Accepted solution]

Hi @KrisIsLearning 

If the goal is to end up with this

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!