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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors