Forum Discussion

IDBdesign's avatar
IDBdesign
Frequent Visitor
6 years ago
Solved

Unable to add new queries after creating custom function

I am trying to add a custom function to a report I am working on, however it seems that after adding the function I can no longer add new blank queries.  I recieve the error message "Something went w...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi IDBdesign 

    I think this is a bug that you should report.

    Problem with your function is an error in the handling of the optional parameter. If you write

                clean_txt =
                    if allowchars <> null then
                        Text.Select(txt, {"a".."z", "A".."Z", "0".."9", " "} & char_list)

    instead of 

                clean_txt =
                    if allowchars <> null then
                        Text.Select(txt, {"a".."z", "A".."Z", "0".."9", " ", char_list})

     

    the function will work with optional parameters as well and you can add more queries.

    But this shouldn't cause inability to add more queries. Therefore I'd recommend to report this as a bug.

     

    Full function code:

     

    let 
    fn_Type = type function (
        txt as (type text meta [
            Documentation.FieldCaption = "Text",
            Documentation.FieldDescription = "<code>Text</code> to sanitize",
            Documentation.SampleValues = {"Hello    World!─â”"}
        ]),
        optional allowchars as (type text meta [
            Documentation.FieldCaption = "Allow Characters",
            Documentation.FieldDescription = "Characters to be ignored by the sanitizer",
            Documentation.SampleValues = {".?!$#"}
        ])
    ) as text meta [
        Documentation.Name = "FlattenText",
        Documentation.DisplayName = "FlattenText",
        Documentation.Description = "Removes non-printable charactes and extra whitespace from <code>text</code>",
        Documentation.LongDescription = "Removes non-printable charactes and extra whitespace from <code>text</code>",
        Documentation.Category = "New Vue Custom Functions | Text",
        Documentation.Author = "Isaac Bickel ([email protected])"
    ],
    fn_Body = (txt as text, optional allowchars as text) as text =>
        let
            char_list = Text.ToList(allowchars),
            clean_txt = 
                if allowchars <> null then
                    Text.Select(txt, {"a".."z", "A".."Z", "0".."9", " "} & char_list)
                else
                    Text.Select(txt, {"a".."z", "A".."Z", "0".."9", " "}),
            list_txt = Text.Split(clean_txt, " "),
            filtered_txt = List.Select(list_txt, each _ <> ""),
            result_txt = Text.Combine(filtered_txt, " ")
        in
            result_txt
in
    Value.ReplaceType(fn_Body, fn_Type)

     

     

    BTW: Happy to see that my introduction of new record fields into the function metadata is spreading 😉

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Hmm, something looks odd to me about your code. I generally keep my function definitions separate or I would think there would have to be multiple let statements. However, I am not the Power Query expert, that's ImkeF 

ImkeF's avatar
ImkeF
Community Champion
6 years ago

Hi IDBdesign 

I think this is a bug that you should report.

Problem with your function is an error in the handling of the optional parameter. If you write

            clean_txt =
                if allowchars <> null then
                    Text.Select(txt, {"a".."z", "A".."Z", "0".."9", " "} & char_list)

instead of 

            clean_txt =
                if allowchars <> null then
                    Text.Select(txt, {"a".."z", "A".."Z", "0".."9", " ", char_list})

 

the function will work with optional parameters as well and you can add more queries.

But this shouldn't cause inability to add more queries. Therefore I'd recommend to report this as a bug.

 

Full function code:

 

let 
    fn_Type = type function (
        txt as (type text meta [
            Documentation.FieldCaption = "Text",
            Documentation.FieldDescription = "<code>Text</code> to sanitize",
            Documentation.SampleValues = {"Hello    World!─â”"}
        ]),
        optional allowchars as (type text meta [
            Documentation.FieldCaption = "Allow Characters",
            Documentation.FieldDescription = "Characters to be ignored by the sanitizer",
            Documentation.SampleValues = {".?!$#"}
        ])
    ) as text meta [
        Documentation.Name = "FlattenText",
        Documentation.DisplayName = "FlattenText",
        Documentation.Description = "Removes non-printable charactes and extra whitespace from <code>text</code>",
        Documentation.LongDescription = "Removes non-printable charactes and extra whitespace from <code>text</code>",
        Documentation.Category = "New Vue Custom Functions | Text",
        Documentation.Author = "Isaac Bickel ([email protected])"
    ],
    fn_Body = (txt as text, optional allowchars as text) as text =>
        let
            char_list = Text.ToList(allowchars),
            clean_txt = 
                if allowchars <> null then
                    Text.Select(txt, {"a".."z", "A".."Z", "0".."9", " "} & char_list)
                else
                    Text.Select(txt, {"a".."z", "A".."Z", "0".."9", " "}),
            list_txt = Text.Split(clean_txt, " "),
            filtered_txt = List.Select(list_txt, each _ <> ""),
            result_txt = Text.Combine(filtered_txt, " ")
        in
            result_txt
in
    Value.ReplaceType(fn_Body, fn_Type)

 

 

BTW: Happy to see that my introduction of new record fields into the function metadata is spreading 😉

  • IDBdesign's avatar
    IDBdesign
    Frequent Visitor
    6 years ago

    HiImkeF 

     

    Thank you for your help.  I've gone ahead and reported it as a bug as you've suggested.  You've also solved an issue with my custom functions optional parameter which I didn't even bring up in the initial post haha.  Thanks! 😁