cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamesyW
New Member

Chart of count options contained in column vs those options

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.

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

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:

7.PNG

 

Then, summarize this table.

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

8.PNG

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Microsoft
Microsoft

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:

7.PNG

 

Then, summarize this table.

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

8.PNG

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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!!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors