Using multiple APIs in Power BI Service

[Anonymous]

I'm trying to get data from multiple APIs (see the script below for 2 of them). In the desktop the query does refresh, but in the service it gives the following error:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

I've read multiple topics about this, but I still don't understand how to fix it, since I need data (project id) from the first API in the second API.

let

headers = [Authorization="XXX"],

base = "https://sentry.io/api/0",

    //Get all project ids and names

    Source = Json.Document(Web.Contents(base&"/projects/", [Headers = headers])),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"id", "name"}),

    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Column1",{"id", "name"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"id", Int64.Type}}),

     //Function GetTeamsData

    GetTeamsData= (page as number) as table =>

    let Source2 = Json.Document(Web.Contents(base&"/projects/COMPANYNAME/" & Number.ToText(page) & "/teams/", [Headers=headers])),

    #"Converted to Table2" = Table.FromList(Source2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table2", "Column1", {"id", "slug"}, {"id", "slug"}),

    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Column2",{{"id", Int64.Type}, {"slug", type text}})

    in

    #"Changed Type2",

    // Use GetTeamsData fuction to get slug

     #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each GetTeamsData([id])),

    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"slug"}, {"slug"})

    in #"Expanded Custom"

[lbendlin]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

[Anonymous]

Hi lbendlin,

Thanks for your response. How do I create variable parameters? I forgot to mention that the list of project ids can increase.

[lbendlin]

Please provide sample data or code that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Anonymous]

The first part (above the line) gives the ids (first column):

These ids need to be inserted in API url of the the second part (below the line), to get the slugs (second column).

Let

headers = [Authorization="XXX"],

base = " https://sentry.io/api/0",

    //Get all project ids and names

    Source = Json.Document(Web.Contents(base&"/projects/", [Headers = headers])),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"id", "name"}),

    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Column1",{"id", "name"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"id", Int64.Type}}),

-------------------------------------------------------------------------------------------------------------------------------------------------------------

     //Function GetTeamsData

    GetTeamsData= (page as number) as table =>

    let Source2 = Json.Document(Web.Contents(base&"/projects/COMPANYNAME/" & Number.ToText(page) & "/teams/", [Headers=headers])),

    #"Converted to Table2" = Table.FromList(Source2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table2", "Column1", {"id", "slug"}, {"id", "slug"}),

    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Column2",{{"id", Int64.Type}, {"slug", type text}})

    in

    #"Changed Type2",

    // Use GetTeamsData fuction to get slug

     #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each GetTeamsData([id])),

    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"slug"}, {"slug"})

    in #"Expanded Custom"