Chart of count options contained in column vs those options

[JamesyW]

I've been using PowerBI for a few months and have created many reports.

However, this query has stumped me.

We have a list, generated by online form, that creates a text field based on  whichever options are selected.

i.e.

If the options were A, B and C.

Then the field might contain:

A

A, B

A, C

A, B, C

B

B, C

etc.

How can I create a chart which counts the instances of each option?

i.e. 

A = 4

B = 4

C = 3

from above.

For the curious... the field data is actually Injury Type from our Accident and Incident notifications.

[v-yulgu-msft · Accepted solution]

Hi @JamesyW,

Fisrt, split the delimited column for multiple rows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1QGSOk5Q2hnGh7Ig4mBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"TextSplit" = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.Split(_,",")}}),
    #"ExpList" = Table.ExpandListColumn(#"TextSplit","Column1")
in
   #"ExpList"

You will get this table:

Then, summarize this table.

Table4 =
SUMMARIZE ( Table3, Table3[Column1], "Total", COUNTA ( Table3[Column1] ) )

Thanks,
Yuliana Gu

[JamesyW]

Thank you Yuliana!

I'd never considered that... with a little tweaking I got it to work and give me exactly what I needed.

Thank you!!