Recursive API Get request from Dynamics BC

[visheshvats1]

Hello, 

I am trying to get data using published endpoints in Dynamics BC. I am not able to pull the entire data set because of page size restriction. I do get the @odata.nextLink in my output json, but I am not able to recursively pull this and combine as an output table. 

My query - 

What am i doing wrong?

@lbendlin 

[v-tsaipranay · Accepted solution]

Hi @visheshvats1 ,

Thank you for reaching out to the Microsoft fabric community forum.

The issue you're experiencing isn't with accessing @odata.nextLink itself, but rather with how the recursive loop is set up in your List.Generate function. Currently, your code retrieves only the first page and doesn't continue through all the remaining pages.

As @vojtechsima  mentioned, it's important to "lag one request behind" so that each iteration checks for @odata.nextLink before proceeding. This approach ensures you reach the final page and avoid stopping prematurely. While their example was for the Power BI Admin API, the same principle applies to Dynamics BC.

Below is a Power Query pattern that you can use for your Dynamics BC endpoint:

let
    GetPage = (url as text) =>
        let
            Response = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & AccessToken]])),
            Data = if Record.HasFields(Response, "value") then Response[value] else {},
            NextLink = if Record.HasFields(Response, "@odata.nextLink") then Response[@odata.nextLink] else null
        in
            [Data = Data, NextLink = NextLink],

    Source = List.Generate(
        ()=> GetPage(BaseUrl),
        each [NextLink] <> null,
        each GetPage([NextLink]),
        each [Data]
    ),

    Output = Table.FromList(List.Combine(Source), Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Output

Hope this helps, Please feel free to reach any further assistance.

Thank you.

[visheshvats1]

Never mind, I was able to fix it by changing Response[@odata.nextLink] to Response[#"@odata.nextLink"]

[visheshvats1]

Thanks for the response, but i'm getting a syntax error. 

[vojtechsima]

@visheshvats1 as @ it's special charcater, use the specific notation:

[#"@odata.nextLink"]

[vojtechsima]

hey, @visheshvats1 ,

I am not lbendlin, but here's a similar call to Microsoft, the key thing is to lag one request behind, so you make sure you fetch the last page as well. Modify the List Generate variable to your need, the @odata.nextLink is the continuationUri.

let
    request = (start as date, end as date) => 
    Json.Document(
        Web.Contents(
            "https://api.powerbi.com", 
            [
                RelativePath = "v1.0/myorg/admin/activityevents", 
                Query = [
                    startDateTime = "'" & Date.ToText(start, "yyyy-MM-dd") & "T00:00:00.000Z'", 
                    endDateTime = "'" & Date.ToText(end, "yyyy-MM-dd") & "T23:59:59.999Z'"
                ], 
                Headers = [
                    Authorization = "Bearer " & fx_bearer()
                ]
            ]
        )
    ),
    nextPageRequest = (uri as text) => 
    Json.Document(
        Web.Contents(
            "https://wabi-west-europe-e-primary-redirect.analysis.windows.net", 
            [
                RelativePath = Text.AfterDelimiter(uri, "/", 2), 
                Headers = [
                    Authorization = "Bearer " & fx_bearer()
                ]
            ]
        )
    ),
    
    getValues = 
    List.Generate(
        ()=> 
        [
            request = request(#date(2025,8,12), #date(2025,8,12)),
            next = request[continuationUri]?,
            isLast = request[lastResultSet]?
        ]
        ,
        (i)=> not i[isLast]?,
        (i)=> 
        [
            request = nextPageRequest(next),
            next = i[request][continuationUri]?,
            isLast = i[request][lastResultSet]?
        ],
        (i)=> i[request][activityEventEntities]
    )

in
getValues