Power Query Custom Function how to select a list

[Dicken]

 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
    Custom1

which 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. 

[Dicken]

Thank you , 

RD

[Anonymous]

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!

[PwerQueryKees]

OK. I have no clue. I have seen this behaviour, but I never use the UI to call a function anyway....

[PwerQueryKees]

I don't understand what you are trying to achieve here. If you like more help, explain what you are trying to do.

[Dicken]

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? 

[dufoq3 · Accepted solution]

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.

[PwerQueryKees]

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

[Dicken]

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
Source

which 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. 

[Dicken]

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 ,