none
Wild characters for text filter RRS feed

  • Question

  • Hello,

    I need to if PQ has a wild character for text filter? For example, I need to filter  that contains G*LSON. * equals a wild character

    Thanks

    Monday, November 9, 2015 3:25 PM

Answers

  • In Power Query, you don’t explicitely write the wild card characters – just describe what needs to be found in the strings:

    = Table.SelectRows(Source, each Text.StartsWith([Text], "G") and Text.Contains([Text], "LSON."))

    Although in the UI you can only pass in 2 different filter criteria, in the formula-editor you can add as many other criteria as you need. You can also combine “and” & “or”-conditions in one filter.

    If there are multiple elements that can only be addressed by “Text.Contains”, this method will not take into account the order of these elements. It’s a bit of a pain, but can be achieved by another column checking: if Text.PositionOf([Text],"LSON") > Text.PositionOf([Text],"G") then 1 else 0. Filter this column on 1.


    Imke Feldmann TheBIccountant.com

    Monday, November 9, 2015 3:48 PM
    Moderator

All replies

  • In Power Query, you don’t explicitely write the wild card characters – just describe what needs to be found in the strings:

    = Table.SelectRows(Source, each Text.StartsWith([Text], "G") and Text.Contains([Text], "LSON."))

    Although in the UI you can only pass in 2 different filter criteria, in the formula-editor you can add as many other criteria as you need. You can also combine “and” & “or”-conditions in one filter.

    If there are multiple elements that can only be addressed by “Text.Contains”, this method will not take into account the order of these elements. It’s a bit of a pain, but can be achieved by another column checking: if Text.PositionOf([Text],"LSON") > Text.PositionOf([Text],"G") then 1 else 0. Filter this column on 1.


    Imke Feldmann TheBIccountant.com

    Monday, November 9, 2015 3:48 PM
    Moderator
  • With reference to Imke :-) comment, this is a code for *g*lson*

    = Table.SelectRows(Sorce , each List.Min(Text.PositionOf([data],"G",Occurrence.All,Comparer.OrdinalIgnoreCase)) < List.Max(Text.PositionOf([data],"LSON",Occurrence.All,Comparer.OrdinalIgnoreCase)))

    As you can see, we can use it directly in filter (but only by writing the code). This code is not case sensitive ( but you can leave the last argument of Text.PositionOf ).

    Wednesday, November 11, 2015 1:03 AM