splitting columns and convert to row

Question

Hi All,

I have a dataset looks like this.

Current Output:

AdTag_IDBrowser_IDeventData_clickthrough_click_abcdeventData_clickthrough_click_efgheventData_service_click_abcdeventData_service_click_efgheventData_service_d2s_abcd1234chrome1    9999firefox  1  2345safari 1   2222safari    1

Instead of having multiple columns such as  clickthrough_'abcd',clickthrough_'efgh'  .... and service_click'abcd' service_click_'efgh' .... , I would like to have 3 main columns clickthrough_click,service_click  and service_d2s with values such as 'abcd', 'efgh' etc.

Desired Output:

AdTag_IDBrowser_IDclickthrough_clickservice_clickservice_d2s1234chromeabcd  9999firefox abcd 2345safariefgh  2222safari  abcd

Thank you so much for help!

Best,

Kutay

JosephSerpis · Accepted Answer

Hi @Kutay I mocked up a workflow that produces the output you describe. Let me know what you think?

Kenda · Accepted Answer

Hi @Kutay

The key here is to transpose the data first so that you can extract what you need from the headers, then cross tab it back to the orientation you need. See the attached workflow for an example.

Hope this helps!