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
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?
What does that mean?
So it becomes a ref list of all categories. [Category] is also the key ref of the Categories table
The Description column of the transaction row is of type Text.
The Key Word column of the Categories table is of type Text.
Yes and yes
Can you give me an example Description and Key Word values that don’t produce the expected result?
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.
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: firstname.lastname@example.org. If not, you might need to reach out to email@example.com directly.
Done, its shared. Thank lots for looking into it. Its a mess still as its far from done.
Ah, try this:
ANY( SELECT( Categories[Category], AND( ISNOTBLANK([Key Word]), CONTAINS([_THISROW].[Description],[Key Word]) ) ) )
Beautifull, its working, thanks so much Steve
Happy to help. Please remember to remove my access to your app.