How to display comma separated column value in diff rows in table visual ?

[pratikrami]

I have below table structure. I nmy dashboard I have added slicer for EMP_NAME. on selection of any EMP, I neeed to display SITES related to it in table visual. how can I display each site in diff row in table visual ?

ID	EMP_NAME	SITES
1	abc	15887910,15821052,15994012,15210925
2	def	15887910,15821052,15994012
3	ghi	15887910,15821052,15994012

[pratikrami]

Hi @OktayPamuk80 ,

Thanks for quick reposnse. So this is the only way ..right ? Because I have other columns also in main table I wanted to avoid these expansion of rows. Is there any way using measure or anything, directly without increasing the rows and we can use the same comma seperated text field in table visual ?

[OktayPamuk80 · Accepted solution]

Hi @pratikrami ,

Sure there is another way. This is the joy of Power BI and data modeling. You can to following:

- On the table (right side), duplicate the table and take only the ID and sites columns.

- Split by columns

- In the first table, you can delete the sites column as this is expanded already in the second table

- Once the data is refreshed you can connect the two tables using ID field

You now have 2 tables, one including ID, Emp_Name and other attributes and a second with just ID and site (expanded.

[rajendraongole1]

Hi @pratikrami  -Please follow the steps at power query editor.

Select the SITES column.
On the ribbon, choose Split Column > By Delimiter.
In the dialog:
Choose Comma ( , ) as delimiter.
Select Split into Rows (not columns).

output:

file FYR attached

[OktayPamuk80]

Hi,

In the Power Query windows, where you prepare the data, you can split the column:

And in the settings spit it into rows:

For each comma separated value, it generate a record:

Regards,

Oktay

If it helped you, would appreciate a KUDOS and mark my answer as a solution 😉