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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors