Hi there.
I'm trying to get info from an API. The result come into several pages with the next page key. I handle it with a list combine but when i try to save I get 'One or more table references a dynamic Data Source. Not able to save dataflow'.
The Web.Contents query is like the documentation suggests.
I can see the data is loaded, but I can't saved it.
let
getMetricsAPIResult = (base_url as text, next_url as text, qty as text, nextPageKey as text, currentList as list) =>
let
apiResult = if nextPageKey = ""
then Json.Document(Web.Contents(base_url,
[
RelativePath = next_url,
Query =
[
pageSize = qty
],
Headers=[Accept="application/json; charset=utf-8", Authorization="Api-Token XXX"]
]
))
else Json.Document(Web.Contents(base_url,
[
RelativePath = next_url,
Query =
[
nextPageKey = nextPageKey
],
Headers=[Accept="application/json; charset=utf-8", Authorization="Api-Token XXX"]
]
)),
newList = List.Combine({currentList, apiResult[metrics]}),
hasNext_tmp = apiResult[nextPageKey],
hasNext = if hasNext_tmp is null
then try apiResult[nextPageKeyError]
else try apiResult[nextPageKey],
returnList = if hasNext[HasError]
then newList
else @getMetricsAPIResult(base_url, next_url, qty, apiResult[nextPageKey], newList)
in
returnList,
consulta = getMetricsAPIResult("https://{env_id}.dynatrace.com", "/api/v2/metrics", "500", "", {}),
#"Converted to table" = Table.FromList(consulta, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to table", "Column1", {"metricId", "displayName", "description", "unit"}, {"metricId", "displayName", "description", "unit"}),
#"Transform columns" = Table.TransformColumnTypes(#"Expanded Column1", {{"metricId", type text}, {"displayName", type text}, {"description", type text}, {"unit", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"metricId", null}, {"displayName", null}, {"description", null}, {"unit", null}})
in
#"Replace errors"
But, if I dont do the "list.combine" to handle the pages, I can save the query.
Dows anyone knows why the first one is considered as a dynamic data source?
let
apiResult = Json.Document(Web.Contents("https://{env_id}.live.dynatrace.com",
[
RelativePath = "/api/v2/metrics",
Query =
[
pageSize = "500",
metricSelector = "xxx, xxx"
],
Headers=[Accept="application/json; charset=utf-8", Authorization="Api-Token XXX"]
]
)),
Navigation = apiResult[metrics],
#"Converted to table" = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to table", "Column1", {"metricId", "displayName", "description", "unit"}, {"metricId", "displayName", "description", "unit"}),
#"Transform columns" = Table.TransformColumnTypes(#"Expanded Column1", {{"metricId", type text}, {"displayName", type text}, {"description", type text}, {"unit", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"metricId", null}, {"displayName", null}, {"description", null}, {"unit", null}})
in
#"Replace errors"
