Pass Text String as Function Name

[rpiboy_1]

Web.Contents is constrained to having a valid URL in the intial arguement in order to be able to refresh automatically in the PowerBI Service. Therefore, if the URL contains a geography the only way I can think of to iterate over multiple geographies, is to create a function that contains the core URL and then arguements for the relative path changes and header information.

So I'll end up with functions named like this:

Fn-US

Fn-CA

Fn-EU

Fn-AU

etc....

I can set-up a table that looks like this:

Geo

US

CA

EU

AU

Now I would like to invoke each custom function as a new column, but I want to use the 'correct' function for Each.

Is there anyway that I can construct/pass the Geo Column to covert it to the M code where I can pass the '#Fn-US':

#"Invoked Custom Function" = Table.AddColumn(Source, "ColumnName", each #"Fn-US"())

[rpiboy_1]

This post also does a nice job of explaining the Expression.Evaluate function further: https://blog.crossjoin.co.uk/2015/02/06/expression-evaluate-in-power-querym/

[rpiboy_1]

@slorin thanks! It never occured to me to look for a function that would execute functions! (duh!). Thanks also for the tip on syntax in terms of '_' versus '-'.

After posting it occured to me that I could also embed IF's into a single function, and pass the geo code for evaluation by the IF and that does seem to work.

For my purposes I think multiple functions (by geo) would be cleaner, however I am curious to know if anyone has feedback on the relative performance and/or peformance penalties of using the execute function with concatentation versus a series of embedded IF statements in a single function. It strikes me that the IF route would impose a 'higher' penalty though it is likely pretty minimal.

[slorin · Accepted solution]

Hi,

With Fn_US, Fn_CA ... and not Fn-US, Fn_CA

= Table.AddColumn(Source, "ColumnName", each Expression.Evaluate("Fn_"&[Geo]&"()",#shared))

With Fn-US

= Table.AddColumn(Source, "ColumnName", each Expression.Evaluate("#(#)""Fn-"&[Geo]&"""()", #shared))

Stéphane