none
Alternative calling function with text string

    Question

  • I'm looking for a way to call a function using a text string (that has been passed via a function) that will work with a refresh in Power BI service. My current methods are:

    1) Record.Field(#shared, "MyFunctionName") or

    2) Expression.Evaluate("MyFunctionName", #shared)

    But both will not be refreshed there.

    Does anyone have an idea (that doesn't involve #shared, as this seems to cause the problem)?

    Thanks!


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, March 15, 2018 8:51 PM
    Moderator

Answers

  • The only way I can think of would be to write a custom function that maps from function names to functions:

    (funcName as text) as function => if funcName = "Foo" then Foo else if funcName = "Bar" then Bar else ...

    Or use a record, and index into it using Record.Field:

    [Foo = Foo, Bar = Bar, ...]

    EDIT: You can also use the record approach with Expression.Evaluate:
    Expression.Evaluate("Foo(123)", [Foo = Foo])

    These approaches should not have the same issues as #shared, since the functions are all explicitly referenced.

    EDIT: I was assuming the issue was unreferenced function queries getting "trimmed" from the evaluation context. But if you're using these functions to do dynamic data source access, see Curt's response below.

    Ehren


    Thursday, March 15, 2018 11:38 PM
    Owner

All replies

  • The only way I can think of would be to write a custom function that maps from function names to functions:

    (funcName as text) as function => if funcName = "Foo" then Foo else if funcName = "Bar" then Bar else ...

    Or use a record, and index into it using Record.Field:

    [Foo = Foo, Bar = Bar, ...]

    EDIT: You can also use the record approach with Expression.Evaluate:
    Expression.Evaluate("Foo(123)", [Foo = Foo])

    These approaches should not have the same issues as #shared, since the functions are all explicitly referenced.

    EDIT: I was assuming the issue was unreferenced function queries getting "trimmed" from the evaluation context. But if you're using these functions to do dynamic data source access, see Curt's response below.

    Ehren


    Thursday, March 15, 2018 11:38 PM
    Owner
  • Thanks Ehren,

    is it possible that #shared might work in the future (and it's just a priority thing) or is it deliberately blocked in the service (I heard things about code injection in the past that I didn't fully understand)?

    Wondering if a function record without the "Accessing data"-functions would be safe to use or which other functions to exclude. (Want to create a record that sits in my function library as an alternative to #shared, so that I don't have to hardcode the record again and again)


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!



    Friday, March 16, 2018 6:38 AM
    Moderator
  • Someone decided in the early days of Power BI that users would never write dynamic queries and that static analysis would therefore be sufficient for determining which data sources were being accessed by a user query. We've been dealing with the complaints about this (and other gaps between Desktop and Service) ever since... :/.

    Consider upvoting related topics on the ideas site. Here were three I found after a quick search:

    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9312540-make-functions-refreshable-when-the-data-source-is

    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10927416-web-contents-should-support-scheduled-refresh-ev

    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9427188-odata-data-refresh-including-custom-power-query

    Friday, March 16, 2018 1:50 PM
    Owner
  • But wait, this report uses Expression.Evaluate from #shared for standard library functions and refreshes in the service... Or there is other issue?

    I mean, do I understand it right, for the certain query, in its #shared scope will be visible/accessible only queries which can be traced via direct referencing (like in query dependencies view)?

    If we have one function fnMyFunction in the report, and the only relation to it inthe current and preceeding (linked) queries is

    Expression.Evaluate("fnMyFunction", #shared)

     then we cannot see it. Moreover, if we have a record of functions like

    FnRecord = [MyFunction=fnMyFunction]

     then we cannot reach it with 

    Expression.Evaluate("FnRecord[MyFunction]", #shared)

    if we didn't directly reference FnRecord somewhere in the query and didn't place it instead of #shared.

    Or there are other reasons? Or even other problem that I didn't catch?


    Maxim Zelensky Excel Inside

    EDIT:

    Oh, got it. But, are my thoughts above correct?

    

    Friday, March 16, 2018 3:24 PM
  • Thanks Curt, voted them all. (didn't realize that these topics were related)

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, March 17, 2018 10:15 AM
    Moderator
  • Don't know if it's directly related, but if the PBI service is using the same SSAS services for refreshing the datasets as Azure Analysis Services ,then #shared used to be accessible from slightly different place. Similar to the one below:

    #sections[main][SharedExpression]

    SharedExpression being a query with just #shared

    https://querypower.com/2017/08/16/exposing-shared-in-azure-analysis-services/

    • Edited by Igor Cotruta Saturday, March 17, 2018 3:58 PM link hyper
    Saturday, March 17, 2018 3:57 PM
  • Thanks Curt, voted them all. (didn't realize that these topics were related)

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Imke, you should note that none of these ideas are even under review. This generally means that the chance of a resolution in your lifetime is about as hopeful as a pregnant teen rubbing with varnishing cream. :)
    Saturday, March 17, 2018 5:14 PM
  • With respect to Azure Analysis Services, I don't know how they manage credentials there. If they don't rely on the same static analysis approach as Power BI then you might get more flexibility.

    With respect to #shared, static analysis reports the use of this symbol and it's my understanding that the refresh configuration simply interprets it as "refresh not supported". I'm not very familiar with the consuming code.

    With respect to the UserVoice site, the number of votes are definitely taken into account when deciding which features to work on. Customer feedback is taken far more seriously than me nagging people and trying to get them to change things.

    Monday, March 19, 2018 9:13 PM
    Owner
  • If someone runs into the same problem: Created a function that creates the (text for the) record automatically, taking the drudgery out of this task: http://www.thebiccountant.com/2018/05/17/automatically-create-function-record-for-expression-evaluate-in-power-bi-and-power-query/

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, May 17, 2018 8:20 PM
    Moderator