Create a Function to Enable Filtering

[Arron_IA]

Hi all,

                I want to create a function which I can then use ‘Invoke Custom Function’ and apply to any column in any table. The function I wish is filtering which is outlined in the script below –

Here is the script applied to a particular table and column

Q8 - Looking for unusual entries in the data fields, e.g. Bank/BSoc Account Name !,",£ etc

let

    Source = Q4,

    // field, e.g. £, $, %

    #"Filter unusual entries Bank/BSoc Account " = Table.SelectRows(Source, each Text.Contains([#"Bank/BSoc Account Name"], "!") or Text.Contains([#"Bank/BSoc Account Name"], "?") or Text.Contains([#"Bank/BSoc Account Name"], """") or Text.Contains([#"Bank/BSoc Account Name"], "£") or Text.Contains([#"Bank/BSoc Account Name"], "$") or Text.Contains([#"Bank/BSoc Account Name"], "%") or Text.Contains([#"Bank/BSoc Account Name"], "^") or Text.Contains([#"Bank/BSoc Account Name"], "*") or Text.Contains([#"Bank/BSoc Account Name"], "(") or Text.Contains([#"Bank/BSoc Account Name"], ")") or Text.Contains([#"Bank/BSoc Account Name"], "_") or Text.Contains([#"Bank/BSoc Account Name"], "=") or Text.Contains([#"Bank/BSoc Account Name"], "+") or Text.Contains([#"Bank/BSoc Account Name"], "`") or Text.Contains([#"Bank/BSoc Account Name"], "¬") or Text.Contains([#"Bank/BSoc Account Name"], "[") or Text.Contains([#"Bank/BSoc Account Name"], "]") or Text.Contains([#"Bank/BSoc Account Name"], "{") or Text.Contains([#"Bank/BSoc Account Name"], "}") or Text.Contains([#"Bank/BSoc Account Name"], "\") or Text.Contains([#"Bank/BSoc Account Name"], "|") or Text.Contains([#"Bank/BSoc Account Name"], "<") or Text.Contains([#"Bank/BSoc Account Name"], ">") or Text.Contains([#"Bank/BSoc Account Name"], "/") or Text.Contains([#"Bank/BSoc Account Name"], "?") or Text.Contains([#"Bank/BSoc Account Name"], ":") or Text.Contains([#"Bank/BSoc Account Name"], ":") or Text.Contains([#"Bank/BSoc Account Name"], "@") or Text.Contains([#"Bank/BSoc Account Name"], "#") or Text.Contains([#"Bank/BSoc Account Name"], "~"))

in

    #"Filter unusual entries Bank/BSoc Account "

Thanks

[BA_Pete · Accepted solution]

Hi @Arron_IA ,

I think the following should be an easier implementation.
Given a list of characters that you want to find (so it's easier to manage/update etc.), you can use the following code:

// SpecialCharsList
let
    Source = {"!", "?", """", "£", "$", "%", "^", "*", "(", ")", "_", "=", "+", "`", "¬", "[", "]", "{", "}", "\\", "|", "<", ">", "/", "?", ":", ":", "@", "#", "~"}
in
    Source

// FindChar
Table.SelectRows(
    PreviousStepName,
    each List.AnyTrue(
        List.Transform(
            SpecialCharsList,
            (x) => Text.Contains([TextColumn], x)
        )
    )
)

Filters this:

...to this:

Pete

[PwerQueryKees · Accepted solution]

I am not at my laptop, so I could not test, BUT

Your function to invoke would be:

(S as text) => List.ContainsAny(Text.ToList(S), Text.Tolist("!?£$%^*()_=+`¬[]{}\|<>/?::@#~"""))

Create a new blank query, open advanced editor and paste the M code above.

Give the query a name. For example HasFunnyCharacters.

Invke it like

#"Filter unusual entries Bank/BSoc Account " = Table.SelectRows(Source, each not HasFunnyCharacters([#"Bank/BSoc Account Name"])

[Arron_IA]

Thanks PwerQueryKees, this solution also works if I wanted to exclude the special characters I just amended the invoking script by removing the "not"  to -

 #"Filter unusual entries Bank/BSoc Account" = Table.SelectRows(#"Changed Type", each #"HasFunnyCharacters"([#"Bank/BSoc Account Name"])

kind regards 

[BA_Pete · Accepted solution]

Hi @Arron_IA ,

I think the following should be an easier implementation.
Given a list of characters that you want to find (so it's easier to manage/update etc.), you can use the following code:

// SpecialCharsList
let
    Source = {"!", "?", """", "£", "$", "%", "^", "*", "(", ")", "_", "=", "+", "`", "¬", "[", "]", "{", "}", "\\", "|", "<", ">", "/", "?", ":", ":", "@", "#", "~"}
in
    Source

// FindChar
Table.SelectRows(
    PreviousStepName,
    each List.AnyTrue(
        List.Transform(
            SpecialCharsList,
            (x) => Text.Contains([TextColumn], x)
        )
    )
)

Filters this:

...to this:

Pete

[Arron_IA]

Thanks Pete that has done the trick 😀