Forum Discussion

as15's avatar
as15
New Member
3 years ago

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; Choic...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
3 years ago

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
    Custom2

 

as15's avatar
as15
New Member
3 years ago

Ok, 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

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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