Filter data table with regex for value

Hi folks, hope someone can help me here:

I have a single data column that I want to filter where every second row contains a variable, but uniform ID-key and every other row contains the name of an event, with the ID-key always being the ID of the event name directly below.

I want to split this column to a new data table so that in the first column I have the ID and in the second column I have the corresponding event name.

I have tried to do this with the filter data table activity by using a regex in the value field to get the ID and filter that to a new data table, but I just can’t get it to work.

Can someone help me out here? There has to be a smart way to do this.

Hi @Westfield,

Try to share example screen short It will be more helpful for us to help you.



Unfortunately, FilterDataTable activity doesn’t support regex.
In this case, we can use LINQ where method and regex as the following, for example.

dt = dt.AsEnumerable.Where(Function(r) System.Text.RegularExpressions.Regex.IsMatch(r("ColumnName").ToString(),"regex pattern")).CopyToDataTable

Hope this helps you.



What I want to do is very simple - here is a screenshot for illustration:


As the print shows, I have one column with two kinds of data which I want to split into two columns with each type of data from the first column.

I have tried multiple solutions, but I keep getting one of the following scenarios:

Either I get the two columns of data like so:

Or like this:


This is really frustrating - I really hope you guys can help.