Forum Discussion
CONTAINSSTRING in-conjunction with LookUpValue
Hello all,
My requirement: I am trying to search for specific words within a column but I am using a Sharepoint list as a data source.
CONTAINSSTRING(<within_text>, <find_text>)
Is it possible to make the <find_text> section dynamic so when you update the Sharepoint list the search parameters update? So you could add more words to the table.
I have attempted to use LookUpValue on its own. This does return some values but only the values if they are on their own. If there is additional text then it won't return the value. So if "lunch" is present then it would pick it up but if "I had lunch" it won't.
Any help or insight you can provide would be much appreciated.
Thanks
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 _resultThis 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.
It's fairly simple (if I understand the addition correctly). Let's say you add another column to the Terms and call it Type; this will act as a family/category for the terms. In the table where you created the Key, you create a new column as below:
Key_Type = LOOKUPVALUE(Terms[Type],Terms[Terms],Claims[Key])You then use this column as a filter (see screenshots)
9 Replies
Could you provide more info about what you're trying to do with your data? There might be an alternative way to get the output you're looking for that will work better. 🙂
Thanks for your reply. I am trying to search for specific words within a column. For example, "lunch","parking","meal","meals","car". I am currently searching for this terms but they are hardcoded in using the SEARCH function. Please note this column is a text column so the keywords aren't necessarily on their own. This is returning the amount of characters to that specific string. I have then used a IF function that if its > 0 then set the result to "true". I don't want to hardcode the searchable keywords into the Power Bi. I need the user to input the keywords into a Sharepoint list which will then search the column "reason for claim" for those specific words. I've started using the CONTAINSTRING function. Hope this makes sense. Thanks again for your help.
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 _resultThis 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.
Thank you for your help. Apologies for the late reply. 👍
