reporting comma separated values in one cell

[baxterj]

I am still in the learning process and I ran into an issue I can’t figure out.  I have 2 tables:

table_role  

 ID                    Role

  1                   Advisor

  2                  Manager

  3                  Director

  4                 Employee

table_review

ID                     present

1                        1,3,4

2                        2,4

3                       1,2,3,4

The goal is to show how many times each role member attended a review. Do the values of table_review.present need to be split before applying a chart?

If not how, can this be visualized in a simple bar chart?

If so, the data is coming in from an online database so I’ll need the process to be automated. Is this possible? If so how?

[TomMartens · Accepted solution]

Hey,

you have to split the comma separated values in your table table_review into separate rows to get something like this

id | present

1 | 1

1 | 3

1 | 4

2 | 2

2 | 4

...

Then you can create a relationship between the tables wiht table_role on the one-side and table_review on the many-side.

You achieve this by using the query editor, please have a look at this post

https://community.powerbi.com/t5/Desktop/How-to-split-the-the-Column-into-Multiple-rows/m-p/255014#M...

 Please be aware that in the above mentioned post the separator "semicolon" is used, just make sure that you choose comma.

In a visual use the column Role as axis and the ID-column from table_review as value, but change the aggregation type from SUM to COUNT by using the context menu on the ID-column after you assigned it to the visual.

Hope this gets you started

Regards

Tom

[baxterj]

I was able to make it do what I needed it to do with your direction. Thank you Tom!

[TomMartens]

Glad, I could assist!

[Interkoubess]

Hi @baxterj,

I don't know for the chart but I will transform my data in Power Query ( split with comma) in order to put the table review data in columns and then I can proceed with a measure or show it with a simple chart.

Can you transform with Power Query, if yes let us know and then I will send the email ( I have not Power Bi right now...).

Otherwise other persons could help you.

Ninter