Filter extraction in Query editor

[kapillehar]

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

[Payeras_BI]

Hi @kapillehar ,

I am with you and would also go for @PhilipTreacy 's solution.

[smpa01]

@kapilleharplease 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"

[PhilipTreacy · Accepted solution]

Hi @kapillehar 

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

[Payeras_BI]

Hi @kapillehar ,

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.

[kapillehar]

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