PowerApps lookups with asterisks in the lookup table

Question

I'm rebuilding an Access database in PowerApps at the client's request. The original database uses a lookup function to return records, but some of the values in the lookup column of the table use an asterisk at the end of the string, allowing that record to return if the string we're searching for starts with those same characters. For example, if the lookup column says "stac*", then searching for anything that starts with "stac" will return that record including "stack", "stacking", "stacy's mom", and "stackoverflow". I'm trying to reproduce this action in PowerApps, but I'm at a loss on how to accomplish this.

A coworker suggested including every possible solution for each of the records that use an asterisk in the string. The lookup table is very long and fully including every possible lookup value, removing the need for the asterisk would multiply the length of the table exponentially and would be very time consuming to create once, much less quarterly when the table is updated.

Answer 1

The second parameter to the LookUp function in Power Apps can use any Power Fx function. In your case, you can use something like the StartsWith function, similar to the example below:

LookUp(<data source>, StartsWith(<column>, "stac"))

Or if some lookup values have asterisks and others don't, you can use something along the lines of

If(
  EndsWith(lookUpValue, "*"),
  LookUp(<data source>, StartsWith(<column>, Left(lookUpValue, Len(lookUpValue) - 1))),
  LookUp(<data source>, <column> = lookUpValue))