How to unpivot different columns

[NathanDrake]

Hi!

Sometimes, users input too many values in just one field.

For example, I am actually trying to go from this (on the G and I column):

To this:

I tried using Cell Splitter, Unpivoting and Joiner, and even Duplicate Row filter, but I keep getting duplicates after all of that:

And also, for example, if I use just the Invoice Number for the Duplicate Row Filter, I just get one value on the B/L Number column. In this example, I have 34 values both on Invoice Number and B/L Number.

Here I share you the workflow example:

ExampleToShare.knwf (99.5 KB)

As you can see, there is also a Container Number field, sometimes it comes with more than one value as well.

Thanks in advance for the help!

[gonhaddock]

Hello @NathanDrake
I’ve been having a look to your data (shared wf). I did a clean up unpivoting the comma listed values, I did correct the missing comma separators as well; but the logic behind joiner and final filter is not too clear for me…

I can share the exploratory workflow with you; you can find it useful.

20230322_pivot_list_columns_ExampleToShare_v0.knwf (44.0 KB)

But my question is… what’s next?

BR

[NathanDrake]

Hi @gonhaddock

Thank you very much for your help.

So basically, nothing’s next

What you have done is what I needed! the only last thing I would need is to also extend the Container Number in all of the rows. I would really appreciate if you can do that last thing. I’m already checking your answer as the Solution.

[gonhaddock]

Thanks for validating the solution @NathanDrake

@NathanDrake

the only last thing I would need is to also extend the Container Number in all of the rows.

Aiming to complete this latest task. You just have to add the column in the Missing Value node configuration; In the same way the other columns do.

BR