Passing Variables into API URL - without preventing PBI Server dataset refresh

Hi, Needed to add a dynamic varaible to my API call used a function to pass the ID number and then pull the relevant field per ID - was very happy when it all seemed to work ok in desktop. Publishe...

Need Help

Anonymous

2 years ago

Hi Anonymous,

Perhaps you can try to use option parameters in web connector if they help you to handle this scenario:

let
    GetProejct=(projectID as number) =>
    let
        projectIDVariabl = projectID,
        root_url = "https://api.workguru.io",
        body = "{ ""ApiKey"": ""XXX"", ""Secret"": ""XXX"" }",
        tokenResponse = Web.Contents(
            root_url,
            [
                Headers = [#"Content-Type" = "application/json"],
                RelativePath = "/api/ClientTokenAuth/Authenticate/api/client/v1/tokenauth",
                Content = Text.ToBinary(body)
            ]
        ),
        AccessToken = Json.Document(tokenResponse)[accessToken],
        projectResponse = Web.Contents(
            root_url,
            [
                RelativePath = "/api/services/app/Project/GetProjectById",
                Headers = [#"content-type": "application/json", authorization = "Bearer " & AccessToken],
                Query = [
                    id = Number.ToText(projectIDVariabl),
                    includeProductImages = "false",
                    includeLineItemCustomFields = "true"
                ]
            ]
        ),
        GetGroups = Json.Document(projectResponse)
    in
        GetGroups
in 
    GetProejct

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code (crossjoin.co.uk)

Chris Webb's BI Blog: Handling Multiple URL Query Parameters With The Same Name Using Web.Contents In Power Query/Power BI (crossjoin.co.uk)

Regards,

Xiaoxin Sheng