Hi All,
I'm trying to flatten my data into only 3 columns, (1 for the line item, 1 for the column name, and 1 for the value). The issue for me right now is that line items that start with 1, have 2 columns. Then line items that start with a 2 have 4 columns and so on. Thus, it makes it difficult to transpose given that each line item has a different amount of columns.
So this is the data originally:
This is the objective (after being flattened)
I have created an Alteryx workflow to solve this (I have attached this below).
Just to explain what I've done:
1) Multi-row Tool: To allocate a Record ID to each line item
2) Filter tool: Filter for each Record ID (can group them if they have the same column headers and the same amount of them)
3) Select tool: Remove any columns with null values
4) Dynamic rename tool: Take the first row as headers
5) Filter tool: Remove the rows where there aren't any values we want to transpose
6) Transpose tool: Make the data flat
7) Union tool: Union all the data together
However, is there anyway to do this without allocating a Record ID and filtering it based on the amount of columns the line item has? I have to do a similar exercise for 40 different data inputs, so it'll take forever for me to configure to record ID tool and the select tool to match each data set.
Thank you for your help
Solved! Go to Solution.
