Jethro
Power Query M language is a powerful tool that allows users to connect to various data sources and transform the data to fit their needs. One such data source is the Jethro data source. Jethro is a high performance SQL-on-Hadoop data engine that enables users to query big data at interactive speeds. In this article, we will explore how to use Power Query M language code for connecting to the Jethro data source from inside Power BI.
Prerequisites
Before we dive into the details of connecting to the Jethro data source, here are a few prerequisites that you need:
1. Power BI Desktop installed on your computer.
2. Access to a Jethro data source.
3. A basic understanding of the Power Query M language.
Step 1: Install the Jethro Connector
To connect to the Jethro data source, you need to install the Jethro Connector for Power BI. You can download the connector from the Jethro website and install it on your computer. Once installed, you can launch Power BI Desktop.
Step 2: Connect to the Jethro Data Source
To connect to the Jethro data source, follow these steps:
1. In Power BI Desktop, click on the “Get Data” button from the Home tab.
2. Select “Jethro” from the list of available connectors and click on “Connect”.
3. In the Jethro Connector dialog box, enter the URL of the Jethro data source and your credentials.
4. Click on “Connect” to establish a connection to the Jethro data source.
Step 3: Query the Jethro Data Source
Now that you have connected to the Jethro data source, you can start querying the data. Power Query M language provides a powerful and flexible way to transform and shape the data to fit your needs. Here are a few examples of how you can use Power Query M language to query the Jethro data source:
Example 1: Filter Data
To filter data from the Jethro data source, you can use the “Filter Rows” transformation. For example, to filter data where the “price” column is greater than 100, you can use the following Power Query M language code:
= Table.SelectRows(#”Jethro Data”, each [price] > 100)
Example 2: Group Data
To group data from the Jethro data source, you can use the “Group By” transformation. For example, to group data by the “category” column and get the sum of the “price” column, you can use the following Power Query M language code:
= Table.Group(#”Jethro Data”, {“category”}, {{“total”, each List.Sum([price]), type number}})
Example 3: Merge Data
To merge data from multiple tables in the Jethro data source, you can use the “Merge” transformation. For example, to merge data from the “sales” and “customers” tables, you can use the following Power Query M language code:
= Table.NestedJoin(#”sales”, {“customer_id”}, #”customers”, {“customer_id”}, “customers”, JoinKind.Inner)
Conclusion
Power Query M language provides a powerful and flexible way to connect to and query the Jethro data source from inside Power BI. With the Jethro Connector for Power BI and a basic understanding of the Power Query M language, you can transform and shape your data to fit your needs and gain insights into your big data.
