Combining columns and filling columns in a table

I have a problem I am not sure how to solve and will give it a shot here on the forum. Thankful for any help I can get!

I have a table which looks like the following:

Product Name Standard Part Optional Part
A A001 B001
Info1 A002 B002
Info2 A003 B003
A004 B004
B C005 D006
Info3 C006 D007
Info4 C007 D008
Info5 C008 D009
C009 D010

I want to manipulate this table into two tables (Table 1 and Table 2). For Table 1 I want to combine the columns Standard Part and Optional Part into one and describe from which column they came from in a new column. Then I want to fill down the product name, in this case A and B, so that I can use it later on in the new format as Table 1. Therefore everything below “A” and “B” in this case should be removed and not filled down. How can I do this?

For Table 2 I want to use the additional info below “A” and “B” to map out these characteristics to keep in a separate table. Is this possible?

My dataset is around a million rows and it contains blank cells below each product (until the next one comes) and depending on if there are more standards parts compared to optional parts (and vice versa) there might be a space until the next product as well (visible in the initial table above).

Table 1

Product Name Part Number Part Type
A A001 Standard
A A002 Standard
A A003 Standard
A A004 Standard
A B001 Optional
A B002 Optional
A B003 Optional
A B004 Optional
A B005 Optional
B C005 Standard
B C006 Standard
B C007 Standard
B C008 Standard
B C009 Standard
B C010 Standard
B D006 Optional
B D007 Optional
B D008 Optional
B D009 Optional
B D010 Optional

Table 2

Product Name Info
A Info1
A Info2
B Info3
B Info4
B Info5

Hi @Polestar01

Take a look at this workflow split table.knwf (117.4 KB). Does it help to re-arrange your table?

I added an extra product to your input file as a last row. I need it to find the start and end row of every product (A/B/…). I also added a table with only the product names, needed as reference in a loop. (I think there are some options to extract it from your own data, GroupBy node).

In the yellow box you will find both output tables.

gr. Hans

1 Like

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