Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
asheet_s
Frequent Visitor

Bar chart for categorical data count

I have a dataset like this:

       UserID     Roles
01ffe0Read, Write, Approve
4abb4Read
6yhc82Approve, Write
02efafWrite
1bcf5dRead, Write

 

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:

 

UserIDRolesReadWriteApprove
01ffe0Read, Write, Approve111
4abb4Read100
6yhc82Approve, Write011
02efafWrite010
1bcf5dRead, Write110

 

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.

 

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 @DataInsights @Ahmedx 

5 REPLIES 5
ppm1
Solution Sage
Solution Sage

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. Here's one way to do it in the query editor.  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"

 

Pat

Microsoft Employee
ichavarria
Solution Specialist
Solution Specialist

Hi @asheet_s,

 

I support @ppm1. This would work. Please give it a try and feel free to reach back if you need any additional help. 

 

 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly

 

 

Hi @ppm1 @ichavarria 

 

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

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.

 

Pat

Microsoft Employee
asheet_s
Frequent Visitor

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 PowerBI

Thanks

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors