RegEx concatenate help

Sorry for such a simple question, but I haven’t found anything online.
I want to select columns that start with either n_ or p_ and case insensitive.

I would then like to concatenate (not add even though these are numbers) the numerical values in columns that match after the “_” so N_1 would pair up with P_1 .

The new column name for the concatenated values should be NP_1, NP_2…

Attached is the raw data I am working with. The Number of N_ and P_ can change per data set which is why I am trying to make this dynamic using RegEx.

The attached excel has the raw data and another tab with the expected output

Thank you

raw_data.xlsx (82.1 KB)

Have you already thought about unpivoting then apply your wildcard/regexfilter and pivot again?

1 Like

Hi @Shmelky, there may an alternative to this but it can certainly be done with a Column List loop as attached, and the columns to be included in the list can be defined using regex. It assumes that the count of P_ columns will always match the N_ columns, so it is driven using the N_ columns.

In your description, as your output columns were named NP_, I was expecting your concatenation to be the N column value followed by the P column value, but in your sample data you put the P_ value first, so that is what my workflow does in the Column Expression.

Concatenate Columns by name.knwf (96.5 KB)


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