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.

     

az38's avatar
az38
Community Champion
6 years ago

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