Forum Discussion

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

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", "C...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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's avatar
PwerQueryKees
Icon for Super User rankSuper User
2 years ago

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's avatar
Dicken
Icon for Post Prodigy rankPost Prodigy
2 years ago

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.