Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
MR17
Frequent Visitor

Using multiple APIs in Power BI Service

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"

4 REPLIES 4
lbendlin
Super User
Super User

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

MR17
Frequent Visitor

Hi lbendlin,

 

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

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

MR17
Frequent Visitor

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

MR17_0-1728910390303.png

 

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"

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.