Cannot save Dataflow due to "entity references to a dynamic datasource"

[ppvinsights]

Hi,

I try to implement a dataflow with a REST api as source. I need to use paging. I now, that I have to use a domain name when using Web.Contents() and use the "RelativePath" in the options of the function for all paging informations. I do exactly that:

(lastPage) => 
  let
    nextPage = lastPage[nextPage],
    urlInfo = lastPage[nextLinkFunc](nextPage),
    Quelle = try Json.Document(Web.Contents("https://somedomain.com", urlInfo[query])),
    result = [
      result       = Quelle[Value][result], 
      nextLinkFunc = lastPage[nextLinkFunc], 
      nextPage     = nextPage + 1
    ]
  in
    result

But Dataflow does not save my work - always the same error that the entity is referencing a dynamic datasource.

Anyone has a functioning paging example using Web.Contents in a dataflow? Or a reason, why this is not working?

Thanks

Holger

[ppvinsights · Accepted solution]

OK, I solved it myself. The problem was the dynamic query part in getNextPage. I was using the record which is updated in the function which is generated in initPage. I changed the function slightly:

(lastPage)=>
let
    nextPage = lastPage[nextPage],
    urlInfo = lastPage[nextLinkFunc](nextPage)
    Quelle = try Json.Document(
        Web.Contents("https://somedomain.com", [RelativePath = urlInfo[relPath]])
    ),
    endTS = DateTime.LocalNow(),
    result = [
        result = Quelle[Value][result], 
        nextLinkFunc=lastPage[nextLinkFunc],
        nextPage= nextPage+1
    ]
in
    result

==> sure, the url part of Web.Contents has to be static

==> in the options of Web.Contents() I create the record in the call directly. Probably the parameter has type "Any" before and this was the problem. I do not know.

Now it is working 🙂

[ppvinsights]

I try to post my code completely:

initPaging:

(tableName as text, limitPerPage as number, optional fields as list) => 
  let
    fieldsToQuery = 
      if fields <> null then 
        List.Accumulate(fields, "", (state, current) => state & "%2C" & current)
      else 
        "",
    getNextlinkFunc = (pageNumber as number) => 
      let
        baseURL = "https://somedomain.com/table/" & tableName,
        queryParms = [
          RelativePath = "?"
            & "?sysparm_exclude_reference_link=true"
            & "&sysparm_fields=fieldsToQuery"
            & "&sysparm_limit="
            & Number.ToText(limitPerPage)
            & "&sysparm_display_value=false"
            & "&sysparm_offset="
            & Number.ToText(limitPerPage * pageNumber)
        ],
        Quelle = [url = baseURL, query = queryParms]
      in
        Quelle,
    result = [result = null, nextLinkFunc = getNextlinkFunc, nextPage = 0]
  in
    result

 getNextPage:

(lastPage) => 
  let
    nextPage = lastPage[nextPage],
    urlInfo = lastPage[nextLinkFunc](nextPage),
    Quelle = try Json.Document(Web.Contents(urlInfo[url], urlInfo[query])),
    result = [
      result       = Quelle[Value][result], 
      nextLinkFunc = lastPage[nextLinkFunc], 
      nextPage     = nextPage + 1
    ]
  in
    result

And put everything together: getTablePaged:

(tablename as text, pageLimit as number, optional fields as list) => 
  let
    result = List.Generate(
        () => initPaging(tablename, pageLimit, fields), 
        (lastPage) => lastPage[result] = null or List.Count(lastPage[result]) > 0, 
        (lastPage) => getNextPage(lastPage), 
        (lastPage) => lastPage
      )
  in
    result

I can understand that changing the URL in "Web.Contents()" could be a problem when a Dataflow tries to investigate if there are dynamic datasources. That is why I changed for testing purposes the code in the function "getNextPage" using a static URL:

(lastPage) => 
  let
    nextPage = lastPage[nextPage],
    urlInfo = lastPage[nextLinkFunc](nextPage),
    Quelle = try Json.Document(Web.Contents("https://somedomain.com", urlInfo[query])),
    result = [
      result       = Quelle[Value][result], 
      nextLinkFunc = lastPage[nextLinkFunc], 
      nextPage     = nextPage + 1
    ]
  in
    result

But anyway: The dataflow could not be saved...

Any help is really appreciated...

Holger

[ibarrau]

Hi. I don't remember having this problem but I have two links just in case. They are examples in Power Bi Desktop, but I think you can use them in dataflow.

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

https://www.thebiccountant.com/2020/05/15/miss-last-page-paging-power-bi-power-query/

Hope one of them can help you.

[ppvinsights]

thanks, but this does not solve my problem...