Data Transformation to create Comma Separated List automatically
Excel Tips and Tricks Power Pivot

Data Transformation to create Comma Separated List automatically

In this article, we have explained how to create an employee-wise skill set list using FILTER and TEXTJOIN Functions. We have explained another tip using the CONCATENATEX function in DAX. We have used Power Pivot to create this.

We have below data in the Excel table:

 

Data in Excel Table
Data in Excel Table

 

Tip 1:

We have used the below function to get the unique employee in ascending order:

=SORT(UNIQUE(Table1[Name]),1)

 

We have used the below formula to get the employee-wise skill set list:

=TEXTJOIN(", ",TRUE,SORT(FILTER(Table1[Skill Set],Table1[Name]='Tip1'!B5),1))

 

Filter and Textjoin Function
Filter and Textjoin Function

 

Tip:2

We have added the table to the data model and loaded it as a Power Pivot. We have created a DAX measure as given below:

 

=CONCATENATEX(Table1,Table1[Skill Set],", ",Table1[Skill Set],ASC)

 

DAX measure
DAX measure

 

Watch the step-by-step video tutorial:

Click here to download the practice file

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 12 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com