Unable to add new queries after creating custom function

[IDBdesign]

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 wrong: an item with the same key has already been added."

Function "FlattenText":

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 (isaac.bickel@new-vue.com)"
    ],
    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)

Appreciate any help I can get on this.  Thanks!

[Greg_Deckler]

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 · Accepted solution]

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 (isaac.bickel@new-vue.com)"
    ],
    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]

Hi@ImkeF 

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! 😁