Forum Discussion

millie23's avatar
millie23
Frequent Visitor
2 years ago
Solved

Trying to do an if function with both contains and doesn't contain

I need something like:

if Text.Contains([Case Subject], "Word1", "Word2", "Word3", "Word4") or not Text.Contains([Case Subject], "Word5", "Word6") then 1 else 0)

 

I basically need an OR function here, and the goal is to basically add up the 1s when it comes to my visualisation. Can someone else?

Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    Hi millie23,

     

    Give this function a go

    (string as text) => 
    if List.Count( Splitter.SplitTextByAnyDelimiter({"Word1", "Word2", "Word3", "Word4"})(string)) >1 
        or List.Count( Splitter.SplitTextByAnyDelimiter({"Word5", "Word6"})(string)) =1 
    then 1 
    else 0

     

    Here's an example of how to implement that into your own code

    let
    fxContains = (string as text) => 
        if List.Count( Splitter.SplitTextByAnyDelimiter({"Word1", "Word2", "Word3", "Word4"})(string)) >1 
            or List.Count( Splitter.SplitTextByAnyDelimiter({"Word5", "Word6"})(string)) =1 
        then 1 
        else 0,
    myTable = #table({"Text"}, {{"Word1Up"}, {"2Word5"}, {"Fish"}}),
    AddCondition = Table.AddColumn( myTable, "test", each fxContains([Text]))
in
    AddCondition

     

    Here's the output of that final query

     

    I hope this is helpful

     

1 Reply

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi millie23,

     

    Give this function a go

    (string as text) => 
    if List.Count( Splitter.SplitTextByAnyDelimiter({"Word1", "Word2", "Word3", "Word4"})(string)) >1 
        or List.Count( Splitter.SplitTextByAnyDelimiter({"Word5", "Word6"})(string)) =1 
    then 1 
    else 0

     

    Here's an example of how to implement that into your own code

    let
    fxContains = (string as text) => 
        if List.Count( Splitter.SplitTextByAnyDelimiter({"Word1", "Word2", "Word3", "Word4"})(string)) >1 
            or List.Count( Splitter.SplitTextByAnyDelimiter({"Word5", "Word6"})(string)) =1 
        then 1 
        else 0,
    myTable = #table({"Text"}, {{"Word1Up"}, {"2Word5"}, {"Fish"}}),
    AddCondition = Table.AddColumn( myTable, "test", each fxContains([Text]))
in
    AddCondition

     

    Here's the output of that final query

     

    I hope this is helpful