The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to Solution.
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!
That's a great solution. Thanks for that.
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!