Power Platform Community Forum Thread Details

How do I match substring to items in a list?

How do I do a Contains match on a list?

I have a microsoft form that auto populates an excel sheet. One question on the form is a multicheck box that allows users to select the various categories that their entry falls under. For each entry, these category tags are saved as a string separated by ";" in a single cell. The data is structured this way by microsoft default and adding a formula to edit this formatting is not supported for excel sheets connected to microsoft forms.

//tags are formatted as:

"Clothing;Banking;Food;"

In my app, I have a table with this data in a gallery and I have a combo box that lists all those categories. I want to be able to filter by categories selected in the combo box. This works right now for entries with a single "tag" but does not work for entries tagged with multiple categories. I am also filtering by many other things. Here's a snippet of what I am using now that does not meet all my needs:

//This is what I'm using right now that only works for entries with a single category tag
SortByColumns(
 Search(
 Filter(
 [@Table1],
 // checkbox filters
 If(
 fee_filter.Value,
 program_fee = "Yes",
 program_fee <> Blank()// fee
 ),
 // combobox filters
 If(
 IsBlank(servicecat_filter.Selected),// service category
 true,
 Service_category in servicecat_filter.SelectedItems
 )
 ),
 // making some fields searchable by text search
 TextSearchBox1.Text,
 "Organization_Name",
),
 "Organization_Name",
 If(
 SortDescending1,
 Descending,
 Ascending
 )
)

I'm not sure how to approach this problem. It seems that `Split` is very difficult for my use case as an entry can have any number of tags, and I'm not sure how to match a string/substring to a list as shown in the code snippet below that throws an error:

// Why doesn't this work? 
// combobox filters
 If(
 IsBlank(servicecat_filter.Selected),// service category
 true,
 IsMatch( Service_category, servicecat_filter.SelectedItems, Contains ), //<--CHANGED***
 )

Any ideas of how I should approach this problem?

Categories:

Building Power Apps