Connecting to internal API in Live or Direct Query mode

[PBIAniket]

Hello All,

I have a internal REST API which is connects to On Prem oracle database.

The connection in Power BI for this API through Web Connector happens in Import Mode.

I am looking for solution or workaround which will enables me to connect to this API in direct query or live connection, because we have a requirement where user will select the values from Slicer/Filter from the Report. These selected values should be pass in the API parameters and API should make a call and bring the data based on selected parameter values. 

Request you all to please share your expert suggestions on how can I acheive Live or Direct Query connection with API.

Can it be done with the help of M-Code, since the conectors like Web, OData Feed or ODBC only supporting import mode of connectivity.

Thanks!

[v-kongfanf-msft · Accepted solution]

Hi @PBIAniket ,

In Power BI, the connectors Web Connector, OData Feed, and ODBC are usually in import mode by default and do not support direct queries or live connections.

However, you can try to accomplish this by doing the following:

1.Using parameterized queries:
In import mode, you can use a parameterized query to pass the values selected by the user in the Slicer/Filter, and then use those parameters in the API call. While this is not a live connection, it can be updated by refreshing the data.

2.Configuring Gateways:
By setting up a gateway, you can refresh data in Power BI Service without public direct access to the internal API.

Refer to below document:

Add or remove a gateway data source - Power BI | Microsoft Learn

Best Regards,

Adamk Kong

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