Forum Discussion

Arron_IA's avatar
Arron_IA
Frequent Visitor
1 year ago
Solved

Create a Function to Enable Filtering

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

Need Help
  • BA_Pete's avatar
    BA_Pete
    1 year ago

    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's avatar
    PwerQueryKees
    1 year ago

    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"])

     

4 Replies

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    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's avatar
      Arron_IA
      Frequent Visitor
      1 year ago

      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's avatar
    BA_Pete
    Super User
    1 year ago

    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's avatar
      Arron_IA
      Frequent Visitor
      1 year ago

      Thanks Pete that has done the trick 😀