Power Query SDK - API REST Paging issue

[DomJohnston]

Hi All,

I'm looking into designing a PowerBI Custom Connector that would allow to connect to a specific REST API.
So far, I was able to authenticate, retrieve results and display them in PowerBI.

Issues begin when I'm trying to introduce paging logic and include some kind of pause between each API call, to give the time to the API to prepare the results.
I would please need your help to have clues on what could be improved in order to fix such situation.
Here's an overview of the said code:

[DataSource.Kind="myFunction", Publish="myFunction.Publish"]

shared myFunction.Contents = () =>

    let

        // Variables

        url = "https://example.com/123/",

        login = "login",

        password = "password",

        start_date = "2022-01-01",

        codetype = "WES",

// Pause definition between each paginated calls

        Pause = () => Duration.From(Duration.Seconds * 10),

        // Call function

        CallAPI = (json_refresh) =>

            let

                // Authentication

                authenticated = Web.Contents(url, [

                    Headers = [

                        #"Content-Type" = "application/json",

                        #"Authorization" = "Basic " & Binary.ToText(Text.ToBinary(login & ":" & password), BinaryEncoding.Base64)

                    ],

                    Content = Json.FromValue(json_refresh)

                ]),

                // Data extraction

                body = Json.Document(authenticated),

                IsTransactionComplete = body[response][isTransactionComplete],

                TransactionUniqueIdentifier = body[response][transactionUniqueIdentifier],

                fullset = body[response][results],

                // Values extraction

                recordtypes = List.Transform(fullset, each try _[entityType] otherwise null),

squareEids = List.Transform(fullset, each try Record.FieldOrDefault(Record.FieldOrDefault(Record.FieldOrDefault(_[square],"data"), "COD,1"), "squareEid") otherwise null),

                dotEids = List.Transform(fullset, each try _[dot][dotEid] otherwise null),

                circleEids = List.Transform(fullset, each try _[circle][circleEid] otherwise null),

                // Record definition

                combinedValues = List.Zip(

                    {

                        recordtypes,

                        squareEids,

                        dotEids,

                        circleEids

                    }

                ),

                records = List.Transform(combinedValues,

                    each [

                        EntityType = _{0},

                        Square.SquareEid = _{1},

                        Dot.DotEid = _{2},

                        Circle.CircleEid = _{3}

                    ]

                ),

                table = Table.FromRecords(records)

            in

                [IsTransactionComplete = IsTransactionComplete, TransactionUniqueIdentifier = TransactionUniqueIdentifier, Table = table],

        // Initial call

        json_refresh = [

            isoDateFrom = start_date,

            codType = codetype

        ],

        initialResult = CallAPI(json_refresh),

        // Loop for next calls

        loop = (result) =>

            let

                isComplete = result[IsTransactionComplete],

                transactionId = result[TransactionUniqueIdentifier]

            in

                if isComplete then

                    result

                else

                    let

                        json_refresh_iteration = [

                            isoDateFrom = start_date,

                            codType = codetype,

                            transactionUniqueIdentifier = transactionId

                        ],

                        refreshedResult = CallAPI(json_refresh_iteration)

                    in

                        refreshedResult,

        // Loop until it ends

        results = List.Generate(

            () => initialResult,

            each [IsTransactionComplete] = false,

            each loop(_)

            each Pause()

        ),

        // And combine results !

        combinedTable = Table.Combine(List.Transform(results, each [Table]))

    in

        combinedTable;

If this can help to understand better:

-A 1st API call is performed. API Calls here are based on JSON POSTs done on an authenticate webservice.

-Based on the response, we lookout if isTransactionComplete is true or false.

-If it is false, then we should perform a paginated call.

-The format of the call is basically the same as the first one, but including a transaction unique identifier gathered through responses obtained.

After the second call over Visual Studio code, the code simply hangs.

Same thing over PowerBI, but after the second paginated call this time (3rd call globally).

I checked on web service side, nothing appears to wait there. The next calls simply don't happen.

Can you please help me and suggest what could be possibly improved ?

Thanks in advance for your help on this !

[Anonymous]

Hi @DomJohnston ,

Regarding the pause between each API call, you can use the Duration.From function to specify the duration of the pause in seconds[2]. For example, Pause = () => Duration.From(Duration.Seconds * 10) will pause for 10 seconds between each API call.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[DomJohnston]

Any idea ? Maybe another approach to consider ?

Thanks !