Is it the right approach to work with REST APIs?

[joaod82]

Hi!

I'm facing some difficuties working with REST APIs. In my solution I've created a PBIX file where I use Power Query to call some APIs that return 1000 records per request and I need to paginate. As I said, I'm doing all of it with Power Query and the "worst" case I have is a table with something about 140k records. Other tables are smaller than this. The PBIX file size is 23mb.

Now the problem ...
This dataset is not too large. But each refresh (on the PBIX file or even in the Power BI Service) is taking too long, sometimes something about 1 hour to complete.

Is this approach the best one for this case? Maybe if I work with another service to load all the data on a SQL Server database and then build my Power BI dataset to consume SQL Server data can provide me more performance ... is this make sense?

Can you guys help me with any suggestions to gain some performance with this data refresh?

I appreciate all the help you can share! Thanks very much!

[v-angzheng-msft]

Hi, @joaod82 

Here are some references, hope to be useful to you：

Refresh your Power BI dataset using Microsoft Flow 

Announcing Data Refresh APIs in the Power BI Service 

Refreshing all datasets in Power BI using REST API 

Unlimited data refresh in Power BI using the REST API and an Azure Function 

Greater control of your Power BI Datasets with the Power BI REST API 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ranbeermakin]

1 hour is not a long time.... pulling 140K records everytime is also not sane... you can pull data using azure functions and dump in a SQL DB... you can also then do incremental refresh for your 140K table data. hope this helps.

some guidelines in my old post here: https://bigintsolutions.com/2019/01/17/power-bi-get-data-apis/

[TomMartens]

Hey @joaod82 ,

whenever I have the possibility to retrieve data from endpoints that demand pagination I tend to use Azure Data Factory (ADF) and write the result to a Azure SQL DB. Of course this comes with additional costs.

Hopefully, this provides some new ideas on how to tackle this challenge.

Regards,

Tom