CONTAINSSTRING in-conjunction with LookUpValue

Hi,

Some assumptions to be made first:

1. I assume the body of the text-columns "live" in a file (which you then import it via Power Query). Let's call this table Statement and the column Description

2. You also have your own means to bring in the Sharepoint list as a query in Power BI. We will call this table SP-List and the column Terms

What you can then do is the following - create a new column in the Statement table:

Key = 
VAR _result = CONCATENATEX(SP-List,
IF(SEARCH(FIRSTNONBLANK(SP-List[Terms],1),Statement[Description],,0)<>0,SP-List[Terms],""))
RETURN
_result

This will return the found term or it will be blank if nothing was found. You can manipulate this by doing:

...
RETURN
IF(ISBLANK(_result),"False","True")

Once you update the Sharepoint list with a new term and bring it in Power BI then with a simple Refresh the new Term will be included in the calculation.

If this solved your problem then please mark it as the solution.