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.
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
Solved! Go to 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 🙂
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 🙂
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
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.
Happy to help!
thanks, but this does not solve my problem...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.