How to pivot data including selected columns into new rows

Hi, I’m trying to work out how to pivot some column data into new rows using KNIME.
My problem, is the data has been laid out so that it becomes a lookup catalogue, which is nice for humans, but not for data work.
In this example data, each row relates to a specific car fitment and can have up to 5 parts available for it. We must look at the columns on the right to see what parts are available.
Parts that start with a 1 need to include the ‘Info 1’ field along with their price and part number and so on.

We want to pivot the data so that each part has its own row.
the raw data looks like this

and the desired outcome for the first 2 lines of input data looks like this

I include sample data here:
car-data-pivot-sample.xlsx (12.9 KB)

If someone could guide us on how to achieve this, that would be wonderful.
thanks,
James

Hi @DWJames
I’ve tried to solve this problem here excel unpivot – KNIME Hub. In the first column of the output I’ve left the “source” row number, just to help you check the results

2 Likes

Wow, this looks great, thank you so much! :smiley:

I’m glad I was able to help!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.