Using txt filter to only show results with a name that contains less than 15 characters.

[royalsirrine]

When using the filter option in excel, we can use the text filter to create a view using the does not contain option and as the value enter ??????????????? to represent 15 characters. This will then show only items that have less than 15 characters in a host name. How can I mimic this kind of filter in PowerBI? It appears the "?" value does not carry over. 

Thx

[v-xiaoyan-msft]

Hi @royalsirrine ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson · Accepted solution]

Using the GUI, apply any text filter to the column, say equals "a".

The query editor will then generate a step with code like 

= Table.SelectRows(#"Changed Type", each [Column1] = "a")

Now replace each [Column1] = "a" with the conditions you actually want.

= Table.SelectRows(#"Changed Type", each Text.Length([Column1]) < 15)

[smpa01]

@royalsirrine  or you can use fully qualified regex. 

/^[A-Za-z]{6}$/gm

returns true for string with exact 6 characters in length

to use the above in PQ

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydnFVitWJVkpMSk5JRbDSwUyQNFAwFgA=", 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}}),
    fx = let   fx =(input)=>
    Web.Page(
        "<script>
            var x='"&input&"';
            var b = x.match(/^[A-Za-z]{6}$/gm);
            document.write(b);
        </script>"){0}[Data]{0}[Children]{1}[Children]    
in
    fx,
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fx([Column1])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Text"}, {"Text"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Text] = "abcdeg"))
in
    #"Filtered Rows"

I wish this was also in DAX.

For complex regex, ask here or stackoverflow is your friend.

[parry2k]

@royalsirrine I don't think wildcard functionality available in Power BI

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡