Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
JimB-GA
Helper III
Helper III

returning a value if substring is found

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

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Hi @JimB-GA

 

There's a very simple way to do it.

You can add a conditional column in edit query.conditional column.png

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.

 

View solution in original post

4 REPLIES 4
V-lianl-msft
Community Support
Community Support

Hi @JimB-GA

 

There's a very simple way to do it.

You can add a conditional column in edit query.conditional column.png

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.

 

Thanks.  A quick and easy solution.

 

Jim

Anonymous
Not applicable

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:
Capture5.PNG
 
Adding filter:
Capture6.PNG
 
 
Let me know if this works.
 
Thanks,
Tejaswi
 
 
az38
Community Champion
Community Champion

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


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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.