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

[Anonymous]

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.
Published to the PowerBI server and got a "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service,"
I had done some research and it seems I need to trick the PowerBI server into not treating this like a dynamic data source, possibly by moving the variable down to Web.contents - but cant get it to work.

Current code is....

(projectIDVariabl) =>

let
url = "https://api.workguru.io/api/ClientTokenAuth/Authenticate/api/client/v1/tokenauth",

body = "{ ""ApiKey"": ""XXX"", ""Secret"": ""XXX"" }",
tokenResponse = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] )),
AccessToken = tokenResponse[accessToken],
AccessTokenHeader = "Bearer " & AccessToken,

data_url = "https://api.workguru.io/api/services/app/Project/GetProjectById?id="& Number.ToText(projectIDVariabl)&"&includeProductImages=false&includeLineItemCustomFields=true",
data_body = "{
""authorization"": """& AccessTokenHeader & """,
""content-type"": ""application/json""
}",

GetGroups = Json.Document(
Web.Contents(
data_url,
[
Headers = Json.Document(data_body)
]
)
),
#"Converted to Table" = Record.ToTable(GetGroups),
Value1 = #"Converted to Table"{0}[Value],
#"Converted to Table1" = Record.ToTable(Value1),
#"Transposed Table" = Table.Transpose(#"Converted to Table1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"tenantId", Int64.Type}, {"projectNo", type text}, {"projectName", type text}, {"description", type text}, {"startDate", type datetime}, {"dueDate", type datetime}, {"clientId", Int64.Type}, {"billingClientId", Int64.Type}, {"projectGroupId", type any}, {"clientPurchaseOrder", type any}, {"status", type text}, {"warehouse", type any}, {"warehouseId", type any}, {"contactId", Int64.Type}, {"contact", type any}, {"billingClient", type any}, {"client", type any}, {"recurringProjectId", type any}, {"recurringProject", type any}, {"customFieldValues", type any}, {"invoices", type any}, {"timeSheets", type any}, {"notes", type any}, {"files", type any}, {"taskLineItems", type any}, {"productLineItems", type any}, {"milestones", type any}, {"stockUsage", type any}, {"clientCreditNotes", type any}, {"completedDate", type any}, {"asset", type any}, {"assetId", type any}, {"purchaseOrders", type any}, {"quoteId", type any}, {"quote", type any}, {"reportingCategoryOption1Id", Int64.Type}, {"reportingCategoryOption2Id", type any}, {"reportingCategoryOption1", type any}, {"reportingCategoryOption2", type any}, {"currency", type text}, {"exchangeRate", Int64.Type}, {"projectGroup", type any}, {"projectManagerId", Int64.Type}, {"projectManager", type any}, {"forecastTime", Int64.Type}, {"totalTime", Int64.Type}, {"total", Int64.Type}, {"baseCurrencyTotal", Int64.Type}, {"documentStorageId", type any}, {"externalSystemId", type any}, {"externalSystemWebhookKey", type any}, {"supplierCreditNotes", type any}, {"useStaffRates", type logical}, {"totalInvoiced", Int64.Type}, {"totalTax", Int64.Type}, {"cost", Int64.Type}, {"forecastCost", Int64.Type}, {"profit", Int64.Type}, {"profitPercentage", Int64.Type}, {"isProductionJob", type logical}, {"phases", type any}, {"customFieldGroupId", type any}, {"customFieldGroup", type any}, {"isDeleted", type logical}, {"deleterUserId", type any}, {"deletionTime", type any}, {"lastModificationTime", type any}, {"lastModifierUserId", type any}, {"creationTime", type datetime}, {"creatorUserId", Int64.Type}, {"id", Int64.Type}}),
#"Expanded customFieldValues" = Table.ExpandListColumn(#"Changed Type", "customFieldValues"),
#"Expanded customFieldValues1" = Table.ExpandRecordColumn(#"Expanded customFieldValues", "customFieldValues", {"customFieldId", "value"}, {"customFieldValues.customFieldId", "customFieldValues.value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded customFieldValues1", each ([customFieldValues.customFieldId] = 2199))
in
#"Filtered Rows"

[v-shex-msft · Accepted solution]

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

[Anonymous]

Thank you so much - that does the job nicely.
I just needed to update the colon to an equals in this line.

 Headers = [#"content-type": "application/json", authorization = "Bearer " & AccessToken],