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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DcJohnston
New Member

Power Query SDK - Issues faced while paging a REST API

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 !

 

1 REPLY 1
DomJohnston
Regular Visitor

No idea on this ?

Maybe an alternative way to propose a customizable connector this way ?

 

Thanks again for your help !

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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