Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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"
Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1
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.
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"