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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
juanmfarinas
Frequent Visitor

API Paginated Result 'One or more table references a dynamic Data Source. Not able to save dataflow'

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"

 

 

 

1 ACCEPTED SOLUTION
juanmfarinas
Frequent Visitor

I could fix it.

Json.Document(Web.Contents(base_url

Web.Contents doesn't admit a variable for the base_url. If I puta a variable I get the error when Try to save. If I put 

Json.Document(Web.Contents("https://{env_id}.live.dynatrace.com",

Works fine!

View solution in original post

2 REPLIES 2
juanmfarinas
Frequent Visitor

Web.Contents admitts a variables, in power app doesn't work well.

juanmfarinas
Frequent Visitor

I could fix it.

Json.Document(Web.Contents(base_url

Web.Contents doesn't admit a variable for the base_url. If I puta a variable I get the error when Try to save. If I put 

Json.Document(Web.Contents("https://{env_id}.live.dynatrace.com",

Works fine!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors