Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to check that a specific text exists between two delimiters with M ?

Hello!

 

I'm looking to write a condition that checks that a text exists between two delimiters (without extraction). Exemple :

 

Data = aaa bbb ccc ddd eee rrr

 

How to check that "ccc" exists between "bbb" and "ddd"?

 

Thanks!

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    You could use Regular Expressions (and ImkeF has a blog on using them), or you could split your string on the space, and check that the delimiters are present and in the correct order.

    i.e:

    Edited to account for missing delimiter as the first element

     

        #"Added Custom" = Table.AddColumn(#"Previous Step", "Matches", each 
        let 
          split = Text.Split([Column with the strings to be tested]," "),
          params = {"bbb"} & {"ccc"} & {"ddd"},
          pos = List.Accumulate(params,{},(x,y)=> x & {List.PositionOf(split,y)})
        in
    in  
          if List.Contains(pos,-1) then false else 
          pos{0} < pos{1} and pos{1} < pos{2}, type logical)

     

     

     

     

4 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous ,
    would that work for you?:

    Text.BetweenDelimiters("aaa bbb ccc ddd eee rrr", "bbb ", " ddd") = "ccc"
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks ! it works fine when there are no values between "ccc" and "bbb" or "ddd"

       

      How do I do the same for this case :

       

      Data = aaa bbb xxx ccc ttt ddd eee rrr

       

      How to verify that "ccc" exists between "bbb" and "ddd"

      • ronrsnfld's avatar
        ronrsnfld
        Icon for Super User rankSuper User
        3 years ago

        You could use Regular Expressions (and ImkeF has a blog on using them), or you could split your string on the space, and check that the delimiters are present and in the correct order.

        i.e:

        Edited to account for missing delimiter as the first element

         

            #"Added Custom" = Table.AddColumn(#"Previous Step", "Matches", each 
        let 
          split = Text.Split([Column with the strings to be tested]," "),
          params = {"bbb"} & {"ccc"} & {"ddd"},
          pos = List.Accumulate(params,{},(x,y)=> x & {List.PositionOf(split,y)})
        in
    in  
          if List.Contains(pos,-1) then false else 
          pos{0} < pos{1} and pos{1} < pos{2}, type logical)