Using Columns-to-Rows before a join.

Richard Illyes
edited December 2023 in General


I'm using version and have encountered the following phenomenon.

When using the Columns-to-Rows step and using a Right-unmatched or Full outer join after it, all the rows that have no match in the Left table are still populated with the first row of the Left table. The expected result, at least for me would be to leave every column coming from the Left table empty, and only have records in the Right columns.

In the example below (JPG and test data attached), you can also see this happening. I am joining the two tables on the Key3(Left) and Key(Right) columns, however the result is populated on the "Left" side as well. If you reverse the inputs and try a left-unmatched join, it yields the same result, only in reversed column order

This does not happen with other steps, but Columns-to-rows seems to "lock" the columns in a way that would make a join not work as expected.

Could you please let me know if there is a way to circumvent this issue?

Thanks in advance.