Using a custom function that references other tables

[pstaggers]

I am trying to get a custom function to work where it will reference data in a column from a table other than the table where the "Invoke Custom Function" command is used.  Does anyone have an idea about how to ensure the column of interest is available for selection?  Thank you.

[AlexisOlson]

Can you post your function code? I'd like to see how each of the inputs is called in your M.

[pstaggers]

By the way, this is trying to make a custom function out of a solution you previously commented on..

https://community.powerbi.com/t5/Desktop/Using-a-Nested-If-with-List-Accumulate-function-to-eliminat...

In this case I have used the code from @Vera_33, but could have also used your suggestion...

Here is the function code.

let
Match = (MergedColumnToSearch as text, CompareTable as table, CompareColumn as text) =>

//consider getting rid of "each" - may be reason this returns a result of "function" rather than an actual result
each

[VAR1=List.Accumulate
(
CompareTable[CompareColumn],
"",
(state, current) =>
if List.Contains(Text.Split( [MergedColumnToSearch], "| "), current)
then state & " " & current
else state
),
VAR2 = if VAR1 = "" then "NOT VAILDATED" else VAR1][VAR2]

in
Match

How it looks in Advanced Editor...

[AlexisOlson]

If you want the function to work on a text and a comparison column, then define a function fn_Match like this

(MergedColumnToSearch as text, CompareColumn as list) as text => 
let 
    VAR1 = List.Accumulate(
        CompareColumn,
        "",
        (state, current) =>
            if List.Contains(Text.Split( MergedColumnToSearch, "| "), current)
            then state & " " & current
            else state
    ),
    VAR2 = if VAR1 = "" then "NOT VAILDATED" else Text.Trim(VAR1)
in
    VAR2

then call it in a query like this:

let
    CompareTable = Table.FromList({"A", "B", "C", "D"}, null, {"CompareColumn"}),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcqxRcK5RcFPSUTJUitWJVoqqUQgHcozAHKcaBb8ahRAg3xjMjwCyTMAsIMMUzAipUXCtUXAE8s2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MergedColumnToSearch = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MergedColumnToSearch", type text}, {"Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fn_Match([MergedColumnToSearch], CompareTable[CompareColumn]), type text)
in
    #"Added Custom"

(I've embedded the comparison table here but you can move that first line to its own query.)

[Vera_33]

Hi @pstaggers 

The record [][] is the same as let...in. I almost never used Invoke Function (only for testing), normally, I just call the custom function directly. You are trying to convert a custom column into a reusable custom function, so I modified it a little bit, considering you have a name for this query, no need to add Match

(MergedColumnToSearch as text, CompareColumn as list) => 

//consider getting rid of "each" - may be reason this returns a result of "function" rather than an actual result


[VAR1=List.Accumulate
(
CompareColumn,
"",
(state, current) =>
if List.Contains(Text.Split( MergedColumnToSearch, "| "), current)
then state & " " & current
else state
),
VAR2 = if VAR1 = "" then "NOT VAILDATED" else VAR1][VAR2]

 so it is the same as

(MergedColumnToSearch as text, CompareColumn as list) => 

//consider getting rid of "each" - may be reason this returns a result of "function" rather than an actual result


let 
VAR1=List.Accumulate
(
CompareColumn,
"",
(state, current) =>
if List.Contains(Text.Split( MergedColumnToSearch, "| "), current)
then state & " " & current
else state
),
VAR2 = if VAR1 = "" then "NOT VAILDATED" else VAR1

in
VAR2

Let me call it fxMatch for now, this is how I call it