Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I found an article that helped me paginate the REST API, however i'm not sure how to use Web.Content() to make the source static while using offset.
This is the code i'm using :
let
EntitiesPerPage = 500,
Url = "https://OurOracleCloudLink.com/hcmRestApi",
GetJson = (Url) =>
let
RawData = Web.Contents(Url,[RelativePath="/resources/11.13.18.05/workers",Query =[limit="500",totalResults="true"]]),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = Url,
Json = GetJson(Url),
Count = Json[#"totalResults"]
in
Count,
GetPage = (Index) =>
let
offset = "&offset=" & Text.From(Index * EntitiesPerPage),
Url = Web.Contents(Url,[RelativePath="/resources/11.13.18.05/workers",Query =[limit="500",totalResults="true",offset=Number.From(Index * EntitiesPerPage)]]),
Json = GetJson(Url),
Value = Json[#"items"]
in
Value,
EntityCount = GetEntityCount(),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
I tried so many different ways, but it always tells me to convert the Offset value to binary or text, but whenever I do that, it tells me that it wants the value in a different type.
Thanks in advance.
@Asalk , Have you already checked these
https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78
Hey @amitchandak ,
I checked the two articles you mentioned, unfortunately I have already checked these before. My main problem is with offset
GetPage = (Index) =>
let
offset = "&offset=" & Text.From(Index * EntitiesPerPage),
Url = Web.Contents(Url,[RelativePath="/resources/11.13.18.05/workers",Query =[limit="500",totalResults="true",offset=Number.From(Index * EntitiesPerPage)]]),
Json = GetJson(Url),
Value = Json[#"items"]
in
Value,
i'm trying to wrap my head around it. this is my first time using REST API as we just have moved to Oracle Cloud, and it seems this is the only way to connect Power BI to SaaS.
Thanks.