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?
br

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)

4 Likes

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