Invoke Custom Function

[r_fioretti]

Hello,

I have different versions for xlsx files, and I’ve created custom function for each version in order to group them.

Before I had more than one version for those xlsx, I had just one function to transform each xlsx and it was working very well. I could invoke it as figure 02 shows.

Now I have one function that indicates with function name power query should invoke to transform the file xlsx, but I can figure out how to invoke it. Figure 03.

Is there any way to call the custom function using a columns value as a parameter?

Figure 01 - Column that indicates which function to invoke.

Figure 02 – Previous query. Works fine for same xlsx versions.

Figure 03 – Current query. Can’t figure out how to invoke the function from column [Função].

[m_dekorte · Accepted solution]

Hi @r_fioretti,

A couple of things I want to call out:

Table.TransformColumns doesn't allow field access beyond the the column you're transforming, instead you can use Table.ReplaceValue.

Inside the [Funcao] I see a list with what appears to be a textual string, not a function. You'll need to access the list and if it is a string pass Expression.Evaluate as well.

Here's an example that illustrates both scenario's:

let
    fxEven = (n) => n+1,
    fxOdd  = (n) => n+1,
    Source = Table.FromColumns( 
        {{1..9}},
        type table [ n =number]
    ),
    AddListWithFunction = Table.AddColumn(Source, "Fx", each if Number.IsEven([n]) then {fxEven} else {fxOdd}),
    ReplaceWithFunction = Table.ReplaceValue(AddListWithFunction,each [n],each Function.Invoke( [Fx]{0}, {[n]} ),Replacer.ReplaceValue,{"n"}),
    AddListWithText = Table.AddColumn(ReplaceWithFunction, "Txt", each if Number.IsEven([n]) then {"fxEven2"} else {"fxOdd2"}),
    ReplaceWithText = Table.ReplaceValue(AddListWithText,each [n],each Function.Invoke( Expression.Evaluate( [Txt]{0}, [fxEven2 = (n) => n+1, fxOdd2  = (n) => n+1] ), {[n]} ),Replacer.ReplaceValue,{"n"})
in
    ReplaceWithText

Ps. If this helps solve your query please mark this post as Solution, thanks!

[m_dekorte · Accepted solution]

Hi @r_fioretti,

A couple of things I want to call out:

Table.TransformColumns doesn't allow field access beyond the the column you're transforming, instead you can use Table.ReplaceValue.

Inside the [Funcao] I see a list with what appears to be a textual string, not a function. You'll need to access the list and if it is a string pass Expression.Evaluate as well.

Here's an example that illustrates both scenario's:

let
    fxEven = (n) => n+1,
    fxOdd  = (n) => n+1,
    Source = Table.FromColumns( 
        {{1..9}},
        type table [ n =number]
    ),
    AddListWithFunction = Table.AddColumn(Source, "Fx", each if Number.IsEven([n]) then {fxEven} else {fxOdd}),
    ReplaceWithFunction = Table.ReplaceValue(AddListWithFunction,each [n],each Function.Invoke( [Fx]{0}, {[n]} ),Replacer.ReplaceValue,{"n"}),
    AddListWithText = Table.AddColumn(ReplaceWithFunction, "Txt", each if Number.IsEven([n]) then {"fxEven2"} else {"fxOdd2"}),
    ReplaceWithText = Table.ReplaceValue(AddListWithText,each [n],each Function.Invoke( Expression.Evaluate( [Txt]{0}, [fxEven2 = (n) => n+1, fxOdd2  = (n) => n+1] ), {[n]} ),Replacer.ReplaceValue,{"n"})
in
    ReplaceWithText

Ps. If this helps solve your query please mark this post as Solution, thanks!

[r_fioretti · Accepted solution]

@m_dekorte 

As you said Table.TransformColumns doesn't allow field access, so I added a new column instead of transforming the [Content] column.