How to use APIs as a data source?

[HarrisonBi]

Hey guys,

I am new to API's and trying to understand best practices for using API's as a data source in Power Bi.

My situation is that I am trying to use data from ServiceNow in Power BI and previously I have used an ODBC connection which is painfully slow. I am now enquiring on how to use the ServiceNow REST API as a data source in Power Bi. I have used the REST API explorer to generate a URL and have authenticated into it as a web source through Power Bi which allows me to see my accounts table data in a table in Power Query Editor.

My questions are:

1. How should I use the API as a data source, does it act as like an SQL query which queries the database and then returns a set of rows. Do I then schedule a refresh for the data to be updated. (I have 11k account records in this table)

2. Why do I get an error when I increase the syssparm_limit to greater than 5000 - why can't I pull all 11k records?

3. Am I on the right track, is this how you would do it? Or can you only push through records from servicenow once they are created rather than refreshing the whole table?

I'm open to any suggestions here or indications if I am on the right track or I should use something else? I've seen there is a Power Bi API as well - do I use that?

Appreciate any help.

Thanks

[Mohit_Chhabra]

Check Below link to refresh data set every second Via API. 

easy and smooth way !!

https://thetrendsettershub.blogspot.com/2023/08/power-bi-tips-api-refresh.html

[antonstorozhuk]

Hello @HarrisonBi

I’d like to propose and alternative solution for you, you may connect your Servicenow tables to Power BI with the help of our add-on - Power BI Connector for ServiceNow:

• https://store.servicenow.com/sn_appstore_store.do#!/store/application/87a42573879e0110fb5033773cbb35...
• https://www.alphaservesp.com/products/servicenow/power-bi/

We have a handy User and Admin Guides for your convenience, but feel free to reach out support team at support@alpha-serve.com if you need any assistance.

Thank you.

Regards,

Anton

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

[v-rzhou-msft · Accepted solution]

Hi @HarrisonBi 

Firstly, which kind of Rest API do you want to use? Just like Power BI, different applications and portals has their own API. If you want to connect to your portal by Rest API in Power BI Desktop, you need to make sure your portal support Rest API. You can try Web connector to connect Rest API in Power BI Desktop. 

For Reference: Using a REST API as a data source

Your data source is in cloud, I think you just need to set data credentials in dataset setting in Power BI Service for schedule refresh. 

Did you mean that when you get your table from API, you will have a row limitation?

Which kind of method did you use to call API, POST or Get?

Rest API may have some limitations: Power BI REST API limitations

You can try to use List.Numbers along with Skip or Offset in the web calls to overcome the row limit. 

This video walks through it.

Power BI - Tales From The Front - REST APIs - YouTube

Best Regards,

Rico Zhou

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