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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
baxterj
Frequent Visitor

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?

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

Glad, I could assist!



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Interkoubess
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.