Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter extraction in Query editor

Hi,

 

I have a column called title that has values like:

1 202 User story

2 2020-2021

 

I just want to filter titles that have 202.

When I use the contain filter, it gives me both the rows since 2020 also has 202. How can i exclude such instances?

I want to do this in M code

 

also note that the 202 substring can be anywhere inside the title

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi Anonymous 

    Try filtering for "202 " , that is a total of 4 characters 202 and a space.

    Or filter to keep 202 and then filter again to remove 20-

    Regards

    Phil

5 Replies

  • Payeras_BI's avatar
    Payeras_BI
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi Anonymous ,

    A possible code-free solution.

    In the example below you need to keep the rows having the substring at the beginning, in the middle or at the end. 

    Duplicate the column, split it into rows by each occurrence of the space delimiter and keep only those that are exact matches of your substring.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Payeras_BI loved your solution and it is more robust. However I guess in terms of ease, PhilipTreacy's solution was better. I wonder how I coudn't figure this out on my own, although handing such cases using a space doesnt make sense. I was expecting regex type of solution but a problem solved is a problem solved.

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous 

    Try filtering for "202 " , that is a total of 4 characters 202 and a space.

    Or filter to keep 202 and then filter again to remove 20-

    Regards

    Phil

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymousplease invoke the following custom function on the column containing 202 string

     

    //function qx
let
  fx=(a)=>
    Web.Page(
        "<script>
            x = '"&a&"';
            y=x.match(/202/gm)
            document.write(y);
        </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text]
             
    
    
in
    fx

     

    . This function has the capacity to extract 202 from any coordinate within a string

    e.g.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwUorViVZKBAEYJykJyKqsrARz9EEgBg6sQQBFtTGIjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each qx([Column1]))
in
    #"Added Custom"