How do you stop Pivot Concatenations from including NULL values?

[Luke Burston]

I have String values in a column that sometimes may be NULL, and I want to Pivot the column into one cell, however if I have say "Red", "Blue", NULL, and "Green" the result turns out as "Red, Blue, , Green" Instead of "Red, Blue, Green".

There are values in other columns of that NULL row that need to be included in the data and so I can't just straight up delete the row, and I don't want to replace the NULL value with something else like "No Colour", I just want it straight up exclude it from being concatenated.

[Gaia Paolini]

I am not getting a ,, effect using the Pivot transformation on the Data Canvas. Can you share a reproducible example? Could it be that your string values are empty strings ('') rather than null?