Split Column of CSV-Like Data into Columns and set there an X

1

Hello Community,

I have following issue and kindly ask for support to solve it :slight_smile:

I have datasets in following format:

Number,Name,Attributes
1,Name 1,"Attr1,Attr2,Attr3"
2,Name 2,"Attr3,Attr4,Attr2"
3,Name 3,"Attr5,Attr1"

… and I want to transform it in this Format

Number,Name,Attr1,Attr2,Attr3,Attr4,Attr5
1,Name 1,X,X,X,,
2,Name 2,,X,X,X,
3,Name 3,X,,,,X

In other words: comma separated data in one column of a data set should be transformed into columns, and a flag should be set in each column according to the csv dataset in that one column.

If transformation is not possible, columns could be also predefined, like in Iterate dataset column by column.

Thank you for your help
Best Regards

2

Hi @MaxEasy,

the result is clearly a matrix table which means that it should be pivoted from a straight table. And the straight table can be obtained by splitting Attributes in your source data into rows.

The less obvious moment here what aggregation function to use for pivoting – it should be the “Any” function.

See the example below:

split-pivot.morph (3.2 KB)
Book1.xlsx (9.4 KB)

image
image.png1055×236 9.89 KB

1 Like
3

Hi dgudkov

Thank you very much. This one solved my issue :slight_smile:

BR MaxEasy