CONTAINSSTRING in-conjunction with LookUpValue

[Andy83]

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

[MNedix · Accepted solution]

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.

[Andy83]

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. 

[MNedix · Accepted solution]

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.

[Andy83]

Hello again, 

If I wanted to enhance this further would it be possible to search by type. If I had a "Type" column within my "SP-List" and the types where defined as "Lunch", "Parking", "Internet" etc. Then these terms (term 1, term 2, term 3) could be allocated a "type". Thank you again for your help. 

[MNedix]

Hi,

I don't think I understand what you're saying. Could you please be more specific? Can you put the outcome in a table or drawing?

[Andy83]

Hi, 

I was just wondering  if it possible to Filter the SP-List by adding a column called "Type" then incorporating this FILTER function within your piece of Dax about so you can just search for the specific terms allocated to a specific type. Hope this makes sense. Thank you.

[MNedix · Accepted 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)

[Andy83]

Thanks so much for your help with this. Much appreciated. 👍

[Andy83]

Thank you for your help. Apologies for the late reply. 👍

[buttertoast]

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. 🙂