Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 !
No idea on this ?
Maybe an alternative way to propose a customizable connector this way ?
Thanks again for your help !
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |