Hi everyone! I’m very new to the KNIME platform but am loving it so far. I am coming from the Excel/Power Query world and have been slowly but surely building up my node knowledge in trying to replicate the steps I’m used to performing when transforming data.
That said, Power Query has the option to remove top/bottom number of rows. Often times I encounter standardized reports that include all kinds of headers before the column headers actually start and then often a few bottom rows as well with grand totals or timestamps, etc.
What would be the KNIME node equivalent to removing top n / bottom n rows from a dataset?
If you know the indexes of the header and the bottom, you can use the regular Row Filter and take the “Include/Exclude rows by number” option.
If you want to remove the first two rows, you could say “Include rows by number”, enter 3 as first row number and tick the “to the end of table” box. Alternatively you could “Exclude rows by number” and enter 1 as first and 2 as last row number.
If you know that you want to keep everything except for the last two rows, but you don’t know the indexes of those rows, you could use the Extract Table Dimension node and a Math Formula (Variable) node (screenshot 1), apply the formula “$${INumber Rows}$$ -2” and the result can be used as a flow variable in the filter (screenshot 2): exclude rows by number, tick the “to the end of table” box and the first row number will be replaced by whatever is the result from the math formula, denoted by the sentence “The “RowRangeStart” parameter is controlled by a variable” at the bottom of the window (3).
In addition to @G47_2’s excellent answer, if you want to remove a variable number of rows because they are missing values in one or more columns at the head or tail of the table, then the Trim Tables node in the Vernalis community extension is useful:
We dont unfortunately. We have a few rather old example workflow, but otherwise probably the best start is to search for ‘Vernalis’ on the KNIME hub:
That way you should be able to see what nodes we have and where they have been used in example workflows, and components. If you have any specific questions, please do get in touch with us either via the forum, or by email at the address which should be in every Vernalis node description.
Thanks Steve,
highly appreciate your answer. I am sure you have some really cool nodes there so I just need to figure out how to use them. I will have a look at the resources. Thanks
bR
