Forum Discussion

baxterj's avatar
baxterj
Frequent Visitor
8 years ago
Solved

reporting comma separated values in one cell

 

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's avatar
    TomMartens
    8 years ago

    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#M113552

     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

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    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#M113552

     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's avatar
      baxterj
      Frequent Visitor
      8 years ago

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

  • Interkoubess's avatar
    Interkoubess
    Solution Sage
    8 years ago

    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