Call REST API on the fly, pass information from Power BI Visualization in the request

[mittalpatel130]

Hello,

There is a need to implement Power BI solution in a complex integration project. Here is the scenario and question - 

1. Data source - NoSQL, with huge data size nearly 1TB. 
2. Use REST API to search through this dataset. API works excellent to perform wild text search on whole dataset. 
3. Plan is to use this All Text Seach REST API in Power BI to search and display results in table.
4. Added custom visual 'Text Search' and a Table visual for search results. 

Questions

1. Can I pass 'string' from Text Search  visual as a request parameter to REST API, to get the results on the fly for end-users? and display results in the table below. 
2. Any type of customized connector or visual we can use to fullfil this requirement in Power BI? 

Other thoughts 

1. There is no point to bring 1TB of data to Power BI to perform this search internally and not using the API. 
2. There is ODBC connection available with DirectQuery capabilities, but here also we need to configure ODBC which ultimately creates a duplicate of dataset and occupies a lot of space on the on-premise server.

Any suggestions?

Thank you.

[v-yingjl · Accepted solution]

Hi @mittalpatel130 ,

1. No, before you use visual to search values in your dataset, all values have been in the underlying data, visual paremeters cannot be passed to api. You can combine parameter query and api in power query first to filter your needed values. Refer these blogs:
   • Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 
   • Specifying JSON Query in Power Query  
   • Working with Web Services in Power Query
2. ODBC is an alternative choice to achieve this. You can take a look at following links about use custom data connector to achieve odbc directquery. In addition, no data is imported or copied into Power BI Desktop under Direct query mode, all DirectQuery requests are sent to the source database, it will nor create a duplicate of dataset.
   • Enabling direct query for a data source via an odbc driver 
   • Enabling Direct Query for an ODBC based connector 

Best Regards,
Yingjie Li

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

[lbendlin]

ODBC Direct Query sounds like your only option.  Why do you think that creates a duplicate dataset?