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.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors