cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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

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

1 ACCEPTED SOLUTION
Helper II
Helper II

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 🙂

View solution in original post

4 REPLIES 4
Helper II
Helper II

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 🙂

View solution in original post

Helper II
Helper II

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

 

Super User II
Super User II

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.



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog


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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors