Answered

Filter Data Based on a Pattern (e.g. Regex)

(0) Share

Report

Posted on by PaulBendall

Is it possible to use the Filter function and use a regular expression as the logical_test? I have had success with Filter and StartsWith but changing StartWith() to IsMatch() which was my assumption for more advanced pattern matching just doesn't seem to work.

For example I have a table that contains the following data in a column, AB-1, AB-1(a), AB-1(b), AB-2, AB-2(1), AB-10, I want to filter or aggregate the data so that I get AB-1, AB-2, AB-10

Many thanks

Paul

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

Pstork1 69,661 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

I haven't tested it, but it should work. The one issue is that it won't be delegable. So it will only work on relatively small, 500-2,000 row data sets.
v-yutliu-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @PaulBendall ,

Do you want to use the value of the IsMatch function as the filter's condition?

Is the filter rule: "AB-"+number?

I’ve made a similar test for your reference:

Here's my collection:

The filter formula: //Test is my collection name

Filter( Test, IsMatch( value, "AB-" &"\d+" ) )

// "\d+" represent one or more numbers.

I think this link will help you a lot:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-ismatch

I suggest you learn the "Predefined patterns" in that doc.

Best regards,
PaulBendall 23 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you @v-yutliu-msft that almost worked. Knowing it could be done enabled me to debug my own solution and spot an error in the way I was filtering, I'm actually filtering a filter. However, in my example I have oversimplified the data in my example. You solution works for the example data I provided but it doesn't work in a situation where the data is something like AB-1(a), AB-1(b), AB-2, AB-2(a), AB-2(b), AB-10..... in this case end up missing out the AB-1 because the dataset doesn't have a distinct AB-1 only AB-1(a) and AB-1(b).

I'm comfortable using basic Regex as I normally script in PowerShell and often use Regex, I am having to learn the syntax is slightly different in PowerApps (no \w or \W but \p{L}), I also re-read the docs and noted that IsMatch uses a Complete Match Option by default so infers ^...$ around any regex supplied

@Pstork1 - thanks for your help, my total data set is just over 1,000 items but in this situation I'm already filtering it down to no more than a few tens of rows

Paul
Verified answer

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @PaulBendall ，

Do you want to get "AB-1, AB-2, AB-10" from "AB-1(a), AB-1(b), AB-2, AB-2(a), AB-2(b), AB-10"?

Since there's no "AB-1" in original data, but you want to get it, I think using IsMatch is not suitable in your case.

IsMatch function is used to filter items based on one pattern, the result should be the existing data in original data.

It will not change data.

What you want is more like cutting data, from "AB-1(a)" to "AB-1".

I suggest you try split() function, which could split data based on one separator.

Try this formula:

Distinct(AddColumns(collection, "data", First(Split(Value,"(")).Result ), data )

If you want to filter collection by using IsMatch firstly, you could try this:

Distinct(AddColumns(Filter(a,IsMatch(Value,"AB-"&"\d+",MatchOptions.Contains)),"data",First(Split(Value,"(")).Result),data)

//However, if all your data is like "AB-....", there's no need to use IsMatch any more. Just using the first formula that I post is enough.

Best regards,