cancel
Showing results for 
Search instead for 
Did you mean: 
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
RicoZhou
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

2 REPLIES 2

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.

RicoZhou
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.