API NEWBEE

[Anonymous]

Hi Guys,

I'm hoping you can help. I've been using Power BI for quite some time and it is what we use as our main reporting tool. The majority of my reporting is done Via SQL and all works well. However, we have just taken on a new CRM (PIPEDRIVE) and they will only give us access to the API not the server. The only other way to extract the data is via exporting csv or excel which is obviously very manual.

I am completely new to API's so i'm wondering what is the best way around this. I've managed to get the API token and tried to pull the data through WEB in power bi but unlike other videos i have watched on API's, it doesn’t split the data out as you would expect so i'm assuming the code will need to be amended via advanced editor in Power BI.

A few questions:

1: What is the best method for extracting API data? Do i need to learn Python or similar. There are loads of youtube videos on API and Python but as far as i can see this is more about writing the API rather than extracting the data?

2. Would learning Power Query M Language be more beneficial?

3. Would using something like Funnel https://funnel.io/api be a solution.

Sorry about the long post but its completely new to me. Thanks

[v-shex-msft · Accepted solution]

Hi @Anonymous,

#1, Nope, you only need to learn the M query to know how to use a web connector to send and receive requests from specific result api with headers and optional parameters.

Below is the link about use web connector to get data from rest API with authorizations: (twitter API)

Pull data from a REST API Authentication 

#2, Yes, power bi desktop backend query tables are using m query functions to get data. If you familiar with M query language, it should help for your scenario.

#3, The service you mention seems like integrate your data source API with their API interface that you still need to learn how to get data from rest API.

If you want to find a simple way, you should search for the supplier that provides the service to packaging rest APIs with a common interface(odata, odbc...) then you can directly use power bi connectors to get the result table records.
Regards,

Xiaoxin Sheng