Hi Everyone! I have a dataset to which the columns might vary month to month. It can vary if there are additional “Plants” being added or a new “Region”. I need to draw the data from the column headers. The excel that I would receive would be like this. The Plant Codes (DC) are grouped by Regions (eg. US, Mexico). So if there are multiple plants in a Region, it will be reflected as “US Vilia - DC 1001/ 1009”. The “Region” is always the 1st word and the “DC Plant Codes” will always be at the end and typically separated by “/”. SKU US Vilia LT2 - DC 1001 US Jones LT2 - DC 1020 US Vilia LT2 - DC 1001/ 1009 US Jones - DC 1020 CAN - DC 1004/ 1014/ 1020 Mexico - DC 1005/ 1013 HK Virtual DC W002 Taiwan DC W005/W700 ABC Logistics Pte Ltd W601 GHTE Virtual DC W602 GHTE Virtual DC W605 A 55 64 - - 20 18 0 24 20 0 16 B 83 72 - - 20 18 0 42 21 0 21 C 83 72 - - 20 18 0 42 21 0 21 I need to unpivot it so that I can derive information from the headers for mapping later on. So I need to find a way to unpivot the above table to the below table. SKU Region DC Qty A US 1001 55 A US 1020 64 A US 1001 - A US 1009 - A US 1020 - A CAN 1004 20 A CAN 1014 20 A CAN 1020 20 A Mexico 1005 18 A Mexico 1013 18 A ABC W002 0 A ABC W005 24 A ABC W700 24 A ABC W601 20 A GHTE W602 0 A GHTE W605 16 I am assuming I should use some sort of regex split but I am not familiar with it :frowning: I have struggling with pivoting and unpivoting and resorted to just creating a table using Table Creator node to just input the values manually. I am very sure there must be an easier way to do this :sob: Please help!! Thank you!!

Splitting Column Headers and unpivoting them to Rows

gonhaddock November 10, 2022, 8:22am 2

Hello @neekstressed
Take a look on this workflow. It can be applied to your use case.

Let us know if some clarifications are needed.
BR

3 Likes