Is it possible to create new column based on a key/value pair in the input file?

[rrapelje]

I have a sample data set that looks like this:

Resource Group	Resource Group tags	Subscription	Subscription tags
aaaa	env=prod;owner=bob;management=partial	wwww	subowner=jack;billing=13982
bbbb	owner=sam;compliance=soc2;management=full	xxxx	subowner=jill;compliance=soc2;billing=13983

I know I can do text to columns and split the Resource Group tags into multiple columns delimited on the semicolon and then I end up with something that looks like this:

Resource Group	Resource Group tags	Subscription	Subscription tags	Resource Group tags 1	Resource Group tags 2	Resource Group tags 3
aaaa	env=prod;owner=bob;management=partial	wwww	subowner=jack;billing=13982	env=prod	owner=bob	management=partial
bbbb	owner=sam;compliance=soc2;management=full	xxxx	subowner=jill;compliance=soc2;billing=13983	owner=sam	compliance=soc2	management=full

Ideally, I would like the column header to look like this:

Resource Group	Resource Group tags	Subscription	Subscription tags	env	owner	compliance	management
aaaa	env=prod;owner=bob;management=partial	wwww	subowner=jack;billing=13982	prod	bob		partial
bbbb	owner=sam;compliance=soc2;management=full	xxxx	subowner=jill;compliance=soc2;billing=13983		sam	soc2	full

[JagdeeshN]

@rrapelje ,

Have you tried using the formula tool with this formula:-

Right([Field1], length([Field1])-FindString([Field1], "=")-1)

This will remove everything before the '=' in your text. For eg 'env=prod' will become 'prod'. You can then use a select tool to rename the columns.

Hope this helps.

Best,

Jagdeesh

[binuacs · Accepted solution]

@rrapelje your method was correct. once you use the text to column tool you need to use the Transpose tool again one more text column tool then the Cross tab tool should do the work here.

[rrapelje]

I appreciate your help and thanks