How can I use an external table column as the argument value for a function parameter

Question

I'm trying to construct a function that will accept a column name from a table other than the table within which the function is invoked, as the parameter argument value.

The function I developed works fine if I use the name of a column that is internal to the table from which I invoke the function, as the parameter argument value. But I would like to pass the name of a column from a different table as the parameter argument value. I can't figure out how to do that.

I have two tables.

Table1: ...and Table2:

My function's code is:

(ListToScan, ListToFind) =>
let
    ListA = {ListToScan}, //{"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {ListToFind}, //{"roo", "me", "only"},
    contains_word=List.Transform(ListA, (lineA)=> List.Transform(ListB, (wordB) => if Text.Contains(lineA, wordB) then wordB else null)),
    GetFoundValues = List.Intersect({ListB, List.Combine(contains_word)})
in
    GetFoundValues

I want to be able to use Table1[Column1] as the parameter argument value for ListToScan and Table2[Column1] as the parameter argument value for ListToFind.

Any ideas?

So it looks like you got a solution in stackoverflow.com/questions/41877469/… ? Please mark the solution as answer, as recognition to Eugene for the help provided. — MarcelBeug
Thanks @MarcelBeug! ...for today's and yesterday's help. I'm not sure whether I'll use your approach or Eugene's in the end, but they are both helpful to me. I did mark Eugene's as the answer a moment ago, but I may end up using yours before all is said and done. — Marc Pincince

MarcelBeug MarcelBeug · Accepted Answer · 2017-01-27T15:41:45

Define you parameters as list, the you can provide table columns as parameter (with invoke custom function you will even be able to select tables and columns for parameters defined as list):

(ListToScan as list, ListToFind as list) =>

How can I use an external table column as the argument value for a function parameter

1 Answers