Forum Discussion

visheshvats1's avatar
visheshvats1
Helper I
11 months ago
Solved

Recursive API Get request from Dynamics BC

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 

 

Need Help
  • Anonymous's avatar
    Anonymous
    11 months ago

    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.

     

5 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    11 months ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    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.