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

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.

     

4 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    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.

     

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

      Thanks.  A quick and easy solution.

       

      Jim

  • 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

  • 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