How to split value in one cell into multiple rows

[ShihMin Huang]

I have a existing data table with the followingcolumns, one is a ID, State and one is a list of IDs, separated by comma.

For example

ID |Region |List1 |List2

---------

1 |A |1, 4, 5 |A1, A2, A3

3 |B |2, 12, 1 |B1, B2, B3I would like to split the column List so that I have a table like this:

ID |Region |List1|List2

---------

1 | A |1 |A1

1 | A |4 |A2

1 | A |5 |A3

3 | B |2 |B1

3 | B |12 |B2

3 | B |1 |B3

[Shashank Gurnalkar]

One way of doing it would be:

To create three calculated columns in the table with the expression as shown below:

1. Select Data > Add...

Source: Data loaded from clipboard.

Data loaded at: 16-04-2020 16:12

Data was added as a new data table

2. Data > Add calculated column...

Column name: L1_L2_1

Expression: Concatenate(Split([List1],",",1),",",Split([List2],",",1))

3. Data > Add calculated column...

Column name: L1_L2_2

Expression: Concatenate(Split([List1],",",2),",",Split([List2],",",2))

4. Data > Add calculated column...

Column name: L1_L2_3

Expression: Concatenate(Split([List1],",",3),",",Split([List2],",",3))

After that, create a table from the current table and transform the data (unpivot) and then add therequired two calculated columns as shown below:

1. Select Data > Add...

Source: Data table from current analysis

Data table: Data Table

Update behavior: Automatic

Data loaded at: 16-04-2020 16:46

Data was added as a new data table

2. Select Data > Transform data...

Added transformations

Transformation name: Unpivot

Columns to pass through:

ID

Region

Columns to transform:

L1_L2_1

L1_L2_2

L1_L2_3

Category column name: Category

Category column data type: String

Value column name: Value

Value column data type: String

Empty values included: Yes

 

3. Data > Add calculated column...

Column name: List1_final

Expression: Split([Value],",",1)

4. Data > Add calculated column...

Column name: List2_final

Expression: Split([Value],",",2)

The final result from the 2nd table will look like this: