returning a value if substring is found

[JimB-GA]

Good afternoon all,

I have a table containing a field called 'Description', and a field called 'Amount'.  I would like to create a field that displays the amount if the description field contains the substring 'dividend'.  The appearance of this substring can appear anywhere in the description field.  I believe I need to use 'Text.Contains' but have been unsuccessful in the syntax usage.

Any and all help is greatly appreciated.

Jim

[V-lianl-msft · Accepted solution]

Hi @JimB-GA

There's a very simple way to do it.

You can add a conditional column in edit query.

Corresponding M query:

= Table.AddColumn(#"Promoted Headers", "Custom", each if Text.Contains([Description], "dividend") then [Amount] else [Description])

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JimB-GA]

Thanks.  A quick and easy solution.

Jim

[tejaswidmello]

HI @JimB-GA ,

calculated column:

Column =

IF (

ISBLANK ( SEARCH ( "Dividend", String[Description], 1, BLANK () ) ),

"1",

"2"

)

And then add the visual level filter to show only 2 in the column.

My output:

Adding filter:

Let me know if this works.

Thanks,

Tejaswi

[az38]

Hi @JimB-GA 

there are 2 different entities:

1. Power Query mode. it work with data source and uses M language. in this mode 'Text.Contains' should be ok

2. DAX mode. This is the mode to create visuals and add business logic to your report. If you want to use this mode you should look at SEARCH (https://docs.microsoft.com/en-us/dax/search-function-dax) or FIND (https://docs.microsoft.com/en-us/dax/find-function-dax) functions

do not hesitate to give a kudo to useful posts and mark solutions as solution