Data transformation is an important aspect of data analysis and is often used to manipulate data in a way that suits our needs. One such manipulation is creating comma-separated lists automatically. In this article, we will discuss how to create employee-wise skill set lists using FILTER, TEXTJOIN, and CONCATENATEX functions.
Understand the Data
Before we dive into the methods, let’s first understand the data we are working with. We have an Excel table that contains the employee’s name and their skill set. Our objective is to create an employee-wise skill set list in a comma-separated format.
We have below data in the Excel table:
Tip 1: Using FILTER and TEXTJOIN Functions
The first method we will discuss with using the FILTER and TEXTJOIN functions in Excel. Let’s assume we want to create a skill set list for the employee named “John.”
You can follow the below steps:
Step 1: Get the Unique Employee List
The first step is to get a list of unique employees in ascending order. We can use the SORT and UNIQUE functions to achieve this. We need to use the below Excel formula-
=SORT(UNIQUE(Table1[Name]),1)
Table1[Name] refers to the column which contains the employee names. The UNIQUE function returns a list of unique values from the specified column, and the SORT function sorts this list in ascending order.
Step 2: Filter and Join the Skill Set
Once we have the list of unique employees, we can use the FILTER and TEXTJOIN functions to filter the skill set for a specific employee and join them with a comma separator.
Below is the formula which we need to use-
=TEXTJOIN(", ",TRUE,SORT(FILTER(Table1[Skill Set],Table1[Name]='John'),1))
Table1[Skill Set] refers to the column that contains the skill set, and ‘John’ is the name of the employee for whom we want to create the skill set list. The FILTER function filters the skill set column based on the employee’s name, and the SORT function sorts this filtered list in ascending order. Finally, the TEXTJOIN function joins this filtered and sorted list with a comma separator.
Tip 2: Using CONCATENATEX Function in DAX
The second method we will discuss with using the CONCATENATEX function in Data Analysis Expressions (DAX). DAXis a formula language used in Power Pivot and Power BI for data modelling and analysis. Let’s assume we have loaded our Excel table into Power Pivot and want to create a skill set lists for all employees. Here are the steps we need to follow:
Step 1: Create a DAX Measure
The first step is to create a DAX measure that concatenates the skill set for all employees.
Below is the formula which we need to use-
=CONCATENATEX(Table1,Table1[Skill Set],", ",Table1[Skill Set],ASC)
Table1 is the name of our table in Power Pivot, and Table1[Skill Set] refers to the column that contains the skill set. The CONCATENATEX function concatenates the values in the specified column with a comma separator (“, “) and returns a single string. The last two arguments specify the column to sort by (Table1[Skill Set]) and the sort order (ASC for ascending).
Step 2: Add the DAX Measure to the PivotTable
Once we have created the DAX measure, we can add it to our PivotTable to see the employee-wise skill set list. We can drag the “Name” column to the Rows area and the DAX measure to the Values area. The PivotTable will show the concatenated skill set for each employee.
Conclusion
In this article, we have discussed two methods to create comma-separated lists automatically using FILTER, TEXTJOIN, and CONCATENATEX functions. The first method uses Excel functions, while the second method uses DAX in Power Pivot. Both methods are effective, and the choice of method depends on the data analysis tool being used and the specific requirements of the analysis.
The first method using FILTER and TEXTJOIN functions is simple and easy to implement in Excel. It works well when we need to create a skill set list for a specific employee or a small number of employees. However, if we need to create a skill set lists for all employees or many employees, it may be time-consuming to repeat the formula for each employee. In such cases, the second method using CONCATENATEX function in DAX is more efficient.
The second method using CONCATENATEX function in DAX is powerful and flexible. It works well when we need to create a skill set lists for all employees or a large number of employees. It also allows us to create more complex measures that involve calculations and filters. However, it requires knowledge of DAX and the use of Power Pivot or Power BI.
In conclusion, creating comma-separated lists automatically is a useful data transformation technique that can save time and effort in data analysis. The methods we have discussed in this blog post using FILTER, TEXTJOIN , and CONCATENATEX functions are just a few of the many ways to achieve this. By understanding the data and the requirements of the analysis, we can choose the most suitable method and create accurate and insightful reports.
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.
Visit our YouTube channel to learn step-by-step video tutorials
