Finding fix text in the string

[cj_sh]

I am fetching data from SharePoint list.

Requirement:

there is a string, from which I need to find two fix words (AD and PC), and send the finding into a new column.

[mussaenda]

Have you solved your problem @cj_sh ?

[Anonymous]

Hi @cj_sh ,

The easiest way to achieve this is Conditional Columns in Power Query. 

Please Use Operator as contains or Equal according to your other options.

Please use the attached picture as reference.

Best Regards,

Gaurav Raj Singh

[mussaenda]

you can do it on power query or dax

in power query, you can create a conditional column and put your condition there.

in dax, you can use containsstring function to find your desired words and use if statement

[cj_sh]

@mussaenda  Thanks, I don't know why my query editor is not showing ContainsString function among the choices.

but I am trying Text.Contains([ColumnName],"AD"),  and it is giving me True/False  whereever AD in the string. 

Now I am getting challenge in value AD in the column instead of True.

Can you help me in that.. 

[danextian]

Hi @cj_sh ,

The language used in Power Query is M while that in the designer is DAX that's why you can't use CONTAINSTRING in the query editor.  For your requirement, you can try this in DAX:

New Column =
//will return blank if both criteria are not met
IF (
    CONTAINSSTRING ( Table[Column], "AD" ) && CONTAINSSTRING ( Table[Column], "PC" ),
    Table[Column]
)

EDIT:

M is case-sensitive.  That's why you are getting a TRUE/FALSE result instead of just either. You can wrap your column first in Text.Upper before using Text.Contains so it would be something like: 

Text.Contains ( Text.Lower ( [Column] ) )

[Anonymous]

Hi @danextian  ,

I think in this situation OR will be the right condition, not AND. 

So in DAX use the below formula,

New Column =
//will return blank if both criteria are not met
IF (
    CONTAINSSTRING ( Table[Column], "AD" ) || CONTAINSSTRING ( Table[Column], "PC" ),
    Table[Column]
)

[v-eachen-msft · Accepted solution]

Hi @cj_sh ,

You could add if() function to return the results you want.

if
Text.Contains([Column1],"PC")=true
then
[Column1]
else
"Your other result"