Turn fields into different columns

zemariajudice

Hello,

I have a dataset with the following characteristics:

Row #1: Transaction A - Location New York

Row #2: Transaction B - Location Seattle

Row #3: Transaction A - Location Washington

Row #4: Transaction B - Location Detroit

...

I would like my dataset to look as follows:

Row #1 - Transaction A - Location 1 New York - Location 2 Washington

Row #2 - Transaction B - Location 1 Seattle - Location 2 Detroit

Basically, I would like to move from two columns (Transaction and Location), to three columns (Transaction, Location 1 and Location 2). What is the easiest way to do this?

Thank you in advance for your help!

Best

Transformation

Accepted answers

michael_treadwell

Use Multi-Row Formula tool to create a unique location number grouped by [Transaction].

Use the Formula tool to create the row headers.

Then use the Crosstab tool to spread the data.

location.yxmd

All comments

michael_treadwell

Use Multi-Row Formula tool to create a unique location number grouped by [Transaction].

Use the Formula tool to create the row headers.

Then use the Crosstab tool to spread the data.

location.yxmd

zemariajudice

Thanks @michael_treadwell!

zemariajudice

Michael,

When I run your flow, I get as many columns as the number of different locations I have in the data. However, I would like to have as many columns as the number of times a transaction appears in the database (e.g. assuming the largest transaction repeats itself five times, five "location" columns would be created), even if the location is the same in all columns.

Do you - or anyone reading this - see a way to solve my issue?

Thank you in advance

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335