Bar chart for categorical data count

Question

I have a dataset like this:&nbsp; &nbsp; &nbsp; &nbsp;UserID&nbsp;&nbsp;&nbsp; &nbsp;Roles01ffe0Read, Write, Approve4abb4Read6yhc82Approve, Write02efafWrite1bcf5dRead, Write&nbsp;How to display the count of users for each role (Read, Write, Approve) in a bar chart?I created a "New Column" for each category as below:&nbsp;UserIDRolesReadWriteApprove01ffe0Read, Write, Approve1114abb4Read1006yhc82Approve, Write01102efafWrite0101bcf5dRead, Write110&nbsp;I created a bar chart that displays the Sum of each of the last 3 columns, but it does not show their Y-axis label. I tried adding the column names to Y-axis but the visual splits-up, which I don't want to happen.&nbsp;This table is imported using a SQL query and will be refreshed daily with new row items hence, need a robust solution.Any suggestions on how to get it right?ichavarria&nbsp;DataInsights&nbsp;Ahmedx&nbsp;

ppm1 · Answer

Your best option is to split the 2nd column at the ", " and create a list, then expand that to new rows. You can then easily create your visual.&nbsp;Here's one way to do it in the query editor.&nbsp;&nbsp;To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBMS0s1UNJRCkpNTNFRCC/KLEnVUXAsKCjKL0tVitWJVjJJTEoygSoAC5hVZiRbGAFFoKqgusByBkapaYlpQDmEkGFScpppCqoNSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, Roles = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Roles], ", ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Roles"})
in
    #"Removed Columns"&nbsp;Pat

ichavarria · Answer

Hi&nbsp;asheet_s,&nbsp;I support&nbsp;ppm1. This would work. Please give it a try and feel free to reach back if you need any additional help.&nbsp;&nbsp;&nbsp;Best regards,&nbsp;Isaac ChavarriaIf this post&nbsp;helps, then please consider&nbsp;Accepting it as the solution&nbsp;and giving Kudos&nbsp;to help the other members find it more quickly&nbsp;&nbsp;

asheet_s · Answer

Hi&nbsp;ppm1&nbsp;ichavarria&nbsp;&nbsp;Thnaks for the repsonse. One question - Where to insert the table name?&nbsp;If I have a table named [X_ROLE_CLASS], where should that name be placed?&nbsp;

ppm1 · Answer

You shouldn't need the table name. In the query editor, the key step is the #"Added custom" one where you reference your Roles column inside Text.Split.&nbsp;Pat

asheet_s · Answer

Hi Pat,Actually the "UserID" I entered in this question is just a sample.If I'm using the UserID column from another table, how do I bring that to the query editor. Your binary64 code translation is something that I'm unable to modify.PS: I'm still have amateur level expertise in PowerBIThanks

Forum Discussion

Bar chart for categorical data count

5 Replies

Related content

Counting nulls

Continuous Categorical Line Chart

Categorical Line Chart with Continuous Lines

PIE Chart - Counting Values

100% stack column chart with categorical counts over time

UserID	Roles
01ffe0	Read, Write, Approve
4abb4	Read
6yhc82	Approve, Write
02efaf	Write
1bcf5d	Read, Write

UserID	Roles	Read	Write	Approve
01ffe0	Read, Write, Approve	1	1	1
4abb4	Read	1	0	0
6yhc82	Approve, Write	0	1	1
02efaf	Write	0	1	0
1bcf5d	Read, Write	1	1	0