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.
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.
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.
User | Count |
---|---|
114 | |
105 | |
79 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |