I’ve searched around to see if I can find out to do this easily but to no avail.
I have 20 columns. Image 1, Image 2, Image 3, up to Image 20. I need to combine these columns into 1 column, separated by a delimiter.
The problem is, not every cell in every row has a value. For example, I may only have 5 images for one row. On another row, Image 1, Image 2, and Image 4 may have content, but we have deleted Image 3 in the past so it is now blank.
With standard concatenate, you end up with [Image 1], [Image 2], - a bunch of commas.
I suppose what I’m trying to do is combine these with a delimiter, but ignore all blank columns.
Other than writing a really long if statement to check if each one is empty for all fields, does anyone have any ideas? Thanks!
You can unpivot the table with the “Unpivot” action, then remove empty cells, then use “Aggregate” in the “Concatenate” mode to concatenate non-empty values. To preserve rows, enumerate before unpivoting.