Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Unable to create dynamic function parameters in Power BI Custom Connector

I am trying to get the dropdown to be populated by another function and am getting a Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException (I believe means forbidden access) or a grayed out dropdown.

 

It seems like there was already someone trying to do this (https://github.com/microsoft/DataConnectors/issues/70 and Use-an-API-Call-to-Populate-Custom-Data-Connector-Settings/), but no one appears to have solved this issue. The main problem I believe is that you are trying to call a function from within a type definition. It doesn't seem to work for me, so does anyone have any ideas?

 

Here is my complete test source code. The API is public so anyone can try running this.

 

// This file contains your Data Connector logic
section IssueTest;

DefaultRequestHeaders = [
    #"Accept" = "application/json"
];

[DataSource.Kind="IssueTest", Publish="IssueTest.Publish"]
shared nothing = () =>
    let 
        a = "Hello From Issue Post"
    in
        a;

[DataSource.Kind="IssueTest"]
shared IssueTest.Contents = Value.ReplaceType(IssueImpl, IssueType);

IssueType = type function (
    optional employee as (type text meta [
        Documentation.FieldCaption = "Pick Employee",
        Documentation.AllowedValues = getEmployees // also tried * = employees which gives (Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException)
    ]))
    as text meta [
        Documentation.Name = "IssueTest"
    ];


IssueImpl = (optional employee as text) =>
    let
        a = getEmployees()
    in
        a;


[DataSource.Kind="IssueTest"]
getEmployees = () =>
    let
        Source = Web.Contents("https://gorest.co.in/public-api/users/"),
        Parsed = Json.Document(Source),
        Result = Parsed[result],
        Table = Table.FromValue(Result),
        Column = Table.RemoveColumns(Table,{"Value"}),
        List = Table.ToList(Column),
        Buffer = List.Buffer(List)
        // Check = Buffer{0} is text // Check if the list is filled with text values
    in
        Buffer;

// This gives a mashup exception (Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException)
// [DataSource.Kind="IssueTest"]
// shared empl = employees;

employees = 
    let
        Source = Web.Contents("https://gorest.co.in/public-api/users/"),
        Parsed = Json.Document(Source),
        Result = Parsed[result],
        Table = Table.FromValue(Result),
        Column = Table.RemoveColumns(Table,{"Value"}),
        List = Table.ToList(Column),
        Buffer = List.Buffer(List)
        // Check = Buffer{0} is text // Check if the list is filled with text values
    in
        Buffer;


// Data Source Kind description
IssueTest = [
    Authentication = [
        // UsernamePassword = [], // will end up needing to use Basic Auth
        Implicit = []
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

// Data Source UI publishing description
IssueTest.Publish = [
    ...
];

IssueTest.Icons = [
    ...
];

 

 

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support

    Hi Anonymous 

    Could you tell what's your aim?

    It's hard to find it from your code.

     

    In addition, you can find how to create dynamic function parameters in Power BI from these links:

    Creating Dynamic Parameters in Power Query

    Dynamic API parameters in Power BI

    Custom Functions Made Easy in Power BI Desktop

     

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Sorry. There also might be some confusion of the word parameter, I mean it as a function parameter when making a query in a custom data connector. I am unable to populate the Documentation.AllowedValues with values returned by an API call. Here is a screenshot:

       

       

      Does that make sense v-juanli-msft ? Thank you for responding quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
            result1 = Json.Document(Web.Contents("https://run.mocky.io/v3/7deabcab-06bf-43f8-8efe-2fbf06eb3988"))[dat],
                
    
    
    
    TypeA = type number meta [
            Documentation.FieldCaption = Text.From(1),
            Documentation.FieldDescription = "Number of times to repeat the message",
            Documentation.AllowedValues = result1
        ],

     

    It works in Power Query Editor