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?

Answer 1

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) =>