OpenSearch Project

Power BI is a business analytics service by Microsoft that provides interactive visualizations and business intelligence capabilities with an interface simple enough for end-users to create their own reports and dashboards. Power Query is a data connection technology that enables users to discover, connect, combine, and refine data from different sources to meet their data analysis needs. In this article, we will explore how to utilize Power Query’s M Language code to connect to the OpenSearch Project Data Source from inside Power BI.

What is OpenSearch?

OpenSearch Project

OpenSearch is a search engine technology that is designed to provide a common and interoperable interface for searching across different websites and repositories. It is a popular search technology used in many different applications and systems, including Amazon Elasticsearch, AWS CloudSearch, and Microsoft Power BI.

Connecting to OpenSearch from Power BI

Power BI has built-in support for connecting to OpenSearch data sources through the Power Query Editor. When connecting to OpenSearch, the first step is to select the “Web” connector and enter the OpenSearch API endpoint URL. This URL should contain the search query and any additional parameters required to retrieve the desired data.

Once the URL has been entered, Power Query will retrieve the data and present it in a preview window. From here, users can apply any necessary transformations to the data and load it into Power BI for further analysis and visualization.

The Power Query M Language Code for OpenSearch Connections

While the Power Query Editor provides a simple and user-friendly interface for connecting to OpenSearch data sources, it is also possible to connect to OpenSearch using the Power Query M Language code. This can be particularly useful for more advanced users who require greater control over the connection process.

OpenSearch Project

The following code demonstrates how to connect to an OpenSearch data source using the Power Query M Language:


Source = Json.Document(Web.Contents(“”)),

#”Converted to Table” = Record.ToTable(Source),

#”Expanded Value” = Table.ExpandRecordColumn(#”Converted to Table”, “Value”, {“id”, “title”, “description”, “url”}, {“id”, “title”, “description”, “url”})


#”Expanded Value”

This code is similar to the code generated by the Power Query Editor when connecting to an OpenSearch data source. It uses the Web.Contents function to retrieve the data from the OpenSearch API endpoint and the Json.Document function to parse the resulting JSON data into a Power Query table.


Connecting to OpenSearch data sources from inside Power BI is a powerful and flexible way to enhance data analysis and visualization capabilities. While the Power Query Editor provides a simple and intuitive interface for connecting to OpenSearch, users can also use the Power Query M Language code to create more advanced and customized data connections. By leveraging the power of OpenSearch and Power BI, users can gain deeper insights into their data and make more informed business decisions.

Power BI Training Courses by G Com Solutions (0800 998 9248)

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)

    Similar Posts