Forum Discussion

HarrisonBi's avatar
HarrisonBi
Frequent Visitor
5 years ago
Solved

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

     

3 Replies