Forum Discussion

JimB-GA's avatar
JimB-GA
Helper III
6 years ago
Solved

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...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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