Forum Discussion
Counting choices in a column
Hi there,
I'm still realitvely new to PowerBI and I'm having trouble counting the number of choices selected for a program.
The table currently looks like this:
Program 1 | Choice 1; Choice 2; Choice 3
Program 2 | Choice 1; Choice 2
Program 3 | Choice 1
What I want on the report is a table that looks like this:
Program 1 | 3
Program 2 | 2
Program 3 | 1
I've been unable to figure out how, it would be a great help if someone can offer a solution.
3 Replies
In a custom column, put following where Column2 should be replaced appropriately
List.Count(Text.Split([Column2], ";"))Test code here
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTy9KzFUwVFDSUVJwzsjPTE5VMLSGsYzgLGOlWB2EciMcylEUGaMoUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), Custom2 = Table.AddColumn(Source, "Count", each List.Count(Text.Split([Column2], ";")), Int64.Type) in Custom2Ok, what happens if I have some missing values on Column 1 and I want it to appear as 0 on the custom column?
So like from this:
Program 1 | Choice 1; Choice 2; Choice 3
Program 2 |
Program 3 | Choice 1
To this:
Program 1 | 3
Program 2 | 0
Program 3 | 1
Use this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTy9KzFUwVFDSUVJwzsjPTE5VMLSGsYzgLGOlWB2EciMcylEUGaMoQpEyAcooxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), Custom1 = Table.AddColumn(Source, "Count", each if [Column2]="" or [Column2]=null then 0 else List.Count(Text.Split([Column2], ";")), Int64.Type) in Custom1
