Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Asalk
Frequent Visitor

How do I convert dynamic source with paginated REST API that uses offset to a static source?

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.

2 REPLIES 2

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.


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.