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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
HarrisonBi
Frequent Visitor

How to use APIs as a data source?

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

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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. 

 

View solution in original post

3 REPLIES 3
Mohit_Chhabra
Regular Visitor

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

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:

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
Community Support
Community Support

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. 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.