How to retrieve data from an Amazon OpenSearch Service (Beta) connector in DirectQuery Mode

[mep]

Documentation says Amazon OpenSearch Service (Beta) connector supports 

• DirectQuery (Power BI Datasets)

source: https://learn.microsoft.com/en-us/power-query/connectors/amazon-opensearch-service

I setup this connection and it works but I can't select the data I need in direct query mode. Previously I used ODBC-based connection with import mode and I could select the data using native query and running SQL. The following are the examples of which one I used and another example of the connection I am trying to switch to.

OpenSearch ODBC DSN; supports Native Query, Doesn't support DirectQuery

let
    Source = Odbc.DataSource("dsn=opensearch"),
    result = Value.NativeQuery(Source, "SELECT * from index_name limit 2;")
in
    result

Amazon OpenSearch Service (Beta); supports DirectQuery, doesn't support Native Query

let
    Source = AmazonOpenSearchService.Contents(<url>, <port>, true),
    result = Source{[Item="index_name",Schema=null,Catalog=null]}[Data]
in
    result

With OpenSearch connector, I can connect to my OpenSearch instance in DQ model, I can see some of my data (by default it returns top 1000 rows), but I have no way to modify the query to select the data I need from the OpenSearch instance. Ideally I want to be able to have a DQ connection to my OpenSearch instance and do full-text searches. 

[Anonymous]

Hi @mep ,

Go to "Get data", choose the connector.

In the Data Connectivity mode, you can choose DirectQuery.

Best Regards,

Stephen Tao

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

[mep]

Thank you Stephen,

I can succesfully setup the connection under the DQ mode as you are showing. However, I'm encountering a limitation with the connector. Once the connection is established, I find myself restricted to retrieving only the top 1000 rows of data. Unfortunately, I don't have the option to access the remaining data or apply filters to control the selection of the top 1000 rows. This limitation significantly hinders my ability to make the most of this connector.