Select expression with CONTAIN

(Hyman van Zyl) #1

Hey Guys,
I want to select a category based on if a key word in present in the description of a transaction. I added a key word column to the category table. I am trying to use this expression but it does not work. If I change the expression to a LIST it lists all the categories.
What am I missing?Thanks
ANY(SELECT(Categories[Category],CONTAINS([Description],[Key Word])))

(Steve Coile) #2

Prefix [Description] with [_THISROW].:

ANY(SELECT(Categories[Category],CONTAINS([_THISROW].[Description],[Key Word])))
(Hyman van Zyl) #3

Hey Steve,
Doesnt want to work, problem stays the same, if I make it LIST instead of Ref it list all the transactions and not only the ones with ey words in the description.
Any other ideas?

(Steve Coile) #4

What does that mean?

(Hyman van Zyl) #5

SELECT(Categories[Category],CONTAINS([_THISROW].[Description],[Key Word]))
So it becomes a ref list of all categories. [Category] is also the key ref of the Categories table

1 Like
(Steve Coile) #6

Please confirm:

  1. The Description column of the transaction row is of type Text.

  2. The Key Word column of the Categories table is of type Text.

(Hyman van Zyl) #7

Yes and yes

(Steve Coile) #8

Can you give me an example Description and Key Word values that don’t produce the expected result?

(Hyman van Zyl) #9

Description: Debit card purchase Pnp Fam Port Alfred 2x-X5-07T12:17:30 x6052
Key Word: Pnp

I tried an alternative way of achieving the same result by using SPLIT on the Description which produces a List of the words in the Description and then using the IN expression. I used this on the same example I provided above and it works generating the Category “Food”. My problem with this method is it limits me to adding only one word within the Description string as a Key Word. I would prefer to use sections.

(Steve Coile) #10

Hmm… I can’t see why this wouldn’t work. Would you want me to examine your app? If so, you’ll need to share it with me: If not, you might need to reach out to directly.

(Hyman van Zyl) #11

Done, its shared. Thank lots for looking into it. Its a mess still as its far from done.

(Steve Coile) #12

Ah, try this:

      ISNOTBLANK([Key Word]),
      CONTAINS([_THISROW].[Description],[Key Word])
(Hyman van Zyl) #13

Beautifull, its working, thanks so much Steve

1 Like
(Steve Coile) #14

Happy to help. Please remember to remove my access to your app.

(Hyman van Zyl) #15

Will do