Taking recurring data row as header

[ndtiongson]

Hi,

A bit new to KNIME, and I’m trying to transform a system report to something workable. The structure of the system report is a bit odd but I’m unable to change it and it looks something like this

But would like to transform it to something like this for further processing

Need to try and take those #,$,% as column names but also change the column name for column B to Dimension. I was able to fix some of the values by using 2 ‘Missing Value’ nodes, one to take the previous value and another to replace the ? from Column B to ‘Current Month’ to reach this output

I have a few ideas to reach the expected output but seems a bit long and repetitive so I wanted to check if some of you have more efficient ideas.

Thanks!

Note: sorry everything is on excel, unable to export workflow due certain restrictions. attached excel test file as well if you want to use the base for reference

Test.xlsx (10.2 KB)

[rfeigel]

Try this. It works with your sample data but probably will be brittle if your input data changes.

KNIME Community Hub

[ActionAndi]

Hi,

in addition to Rfeigel and if the columns for #, $, % are static you can use following workflow