Forum Discussion
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 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 ([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)
Appreciate any help I can get on this. Thanks!
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 😉
3 Replies
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
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 😉
