cancel
Showing results for 
Search instead for 
Did you mean: 
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

tejaswidmello
Impactful Individual
Impactful Individual

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
Super User
Super User

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!