Web API based Direct Query Mode Custom Power BI Connector

[md11071]

Hello Everyone,

Problem Statement:
We need to enable real-time data access and analysis in Power BI using DirectQuery mode to ensure up-to-date insights from a Web API.

Approach Tried:
We developed a custom connector using the Power Query SDK to fetch data from a Web API and integrate it into Power BI. While it functions well in Import mode, attempting to use DirectQuery results in the following error:

"This query contains transformations that can't be used for DirectQuery."

Has anyone faced a similar issue? If so, what steps did you take to resolve it?

Thanks!

[Poojara_D12 · Accepted solution]

Hi @md11071 

DirectQuery mode in Power BI imposes strict limitations on transformations, making it difficult to use with Web APIs, as they often return JSON data requiring processing in Power Query. If a custom connector is used, it must support query folding, meaning all transformations should be translatable to SQL-like queries, which is rarely feasible for APIs. To enable near real-time updates, an alternative approach is using scheduled refresh with an on-premises data gateway, allowing frequent updates (as often as every minute in Power BI Premium). A better solution is to store API data in a relational database like Azure SQL, Dataverse, or Synapse Analytics, and connect Power BI to it using DirectQuery, ensuring smooth performance. For true real-time updates, Power BI Push/Streaming Datasets can be leveraged, allowing API data to be pushed directly into Power BI without relying on DirectQuery. If DirectQuery is essential, custom connectors must be optimized by reducing transformations and ensuring 

EnableDirectQuery=true is correctly set. The best approach depends on the specific requirements, balancing performance, real-time needs, and API constraints.

[md11071]

Hello,

Thanks for the clarification. 

We have enabled the Direct Query in the connector in the custom connector created.

We will look into the alternatives suggested.

Thanks!!