Converting rows to columns - data preparation for advance analytics and machine learning

[Sall]

I have a table with values extracted from unstructured data that looks somewhat like this:

Country 1	country 2	Country 3	Country 4	Country 5	Country 6
Germany	Turkey	null	null	null	Null
Denmark	Spain	Portugal	Turkey	null	Null
Romania	Germany	null	null	null	Null
Romania	null	null	null	null	Null

In order to use the data for machine learning and advance analytics I need to convert it to this structure:

Germany	Denmark	Romania	Turkey	Spain	Portugal
1	0	0	1	0	0
0	1	0	1	1	1
1	0	1	0	0	0
0	0	1	0	0	0

I have being working with SQL statements, cursors, ifs' and more in order to solve this problem. And it is a little pain in the a**. It can literally save me a ton if I could Alteryx it somehow.

Thanks in advance to some of the great minds around here

[Kenda · Accepted solution]

Hey @Sall!

This problem shouldn't be too hard in Alteryx. I have attached a workflow that details how I would solve it. I start by adding a RecordID tool. Then the key is to transpose the data. After that, it is just some clean up and then using a CrossTab to get the data back with the countries as the headers.

Hope this helps!

[DavidxL · Accepted solution]

Was just about to post but @Kenda beat me to the punch! Only thing I did differently was using the Data Cleansing tool at the end instead of Multi-Field Formula, but both come up withe same answer.

[Sall]

Amazing.Thank you!
I'm quite new to the Alteryx world. and I am loving it.