Forum Discussion
Power Query Custom Function how to select a list
HI,
Is it possible to select a list the same way you can a table when creating a custom function, so starting pont;
nested list old / new values;
rplace =
{ { "cat", "CAT"}, {"cow", "COW"}}
= List.Accumulate( Table.ColumnNames( Source), Source,
(s,c)=> Table.TransformColumns( s,
{ c, each List.ReplaceMatchingItems(
Splitter.SplitTextByWhitespace() (_), rplace )
}
) )i then made this a functon;
(atable as table, new )=>
let
Custom1 = List.Accumulate( Table.ColumnNames( atable ), atable ,
(s,c)=> Table.TransformColumns( s,
{ c, each List.ReplaceMatchingItems(
Splitter.SplitTextByWhitespace() (_), new )
}
) )
in
Custom1which can be invoked and just select the table and type the name of the replacement list in the dialog box, but is there a way to select it,
defining as a list will not work, as this then asks for table / column to use as list?
Richard.
Check this:
= (myTable as table, column as text)=> Table.TransformColumns(myTable, {{column, each Text.Combine(List.ReplaceMatchingItems(Text.Split(_, " "), {{"cat", "CAT"}, {"dog", "DOG"}}), " ")}})Now you can invoke this function by selecting a table with dropdown list and entering Column Name as text.
9 Replies
not sure what you mean by ' select' it.
Guessing: You can call a function from anothe query of function with its name and the parameter in parenthesis.
In your case something like
rplace = { { "cat", "CAT"}, {"cow", "COW"}} = replaceallmatching(Source, rplace )assuming you named your function replaceallmatching
What I mean is choose it form the invoke dialog box when you click/ select / choose a funtion a dialog box opens, form there ( x as table)=> you can choose a table , so I want to choose my replacement list in the same manner, so ( table as table, replacement list as ? ) => list does work as that askes for a table column, you cannot type the name in as that = text, so the only way so far is to just do it manually
-= functionName( tablename, listname), but is there a way to use the dialog box ,
Just to add some experimnting but no luck, I thoght if I made it a tabel I could select it , so
Table.FromList( {5..7},
(A)=> {A} , type table [V = list] )= (atable as table ,C as text, Y as list )=>
let
Source = List.ContainsAll( atable, C, Y )
in
Sourcewhich looks like it should work but didn't,
the result is Query1(test, "A", Y[V]) , so text = table , "A" = column, Y[V] = numbers
not sure why doesn't work.
I don't understand what you are trying to achieve here. If you like more help, explain what you are trying to do.
Ok, last try,
I create a custom function, and when you select that function you get to choose input parameters,so my fiirt is a table, this can be chosen form a drop down list of avialable tables, so in the doalog box
(atable as table, oldnew as list) =>
how do I choose the list, as it now asks for a table column?
Check this:
= (myTable as table, column as text)=> Table.TransformColumns(myTable, {{column, each Text.Combine(List.ReplaceMatchingItems(Text.Split(_, " "), {{"cat", "CAT"}, {"dog", "DOG"}}), " ")}})Now you can invoke this function by selecting a table with dropdown list and entering Column Name as text.
OK. I have no clue. I have seen this behaviour, but I never use the UI to call a function anyway....
Hi Dicken
I'm afraid we cannot change how the dialog box works currently. Besides custom functions, for the built-in functions which contains a list parameter, it also requires choosing a column from a query when it's invoked by using UI. As of now, the UI is designed like this thus we cannot change its behavior. You will have to invoke it with M code manually instead of using the UI if you want to use a list for a parameter.
I find a similar request at Fabric Ideas forum, you can vote it up through the following link: Microsoft Idea: User-Defined Function Invocation Dialog Doesn't Fully Support List Parameters
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!Thank you ,
RD
