Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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"())
Solved! Go to 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
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/
@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.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.