Azure Databricks
Power Query is a powerful data transformation and connection tool that allows users to connect to multiple data sources and transform data according to their needs. One of the data sources that Power Query can connect to is Azure Databricks, a fast, easy, and collaborative Apache Spark-based analytics platform. In this article, we will explore the Power Query M Language Code for Connecting to the Azure Databricks Data Source from Inside Power BI.
Prerequisites
Before we start, there are a few prerequisites that need to be fulfilled. Firstly, you need to have an active Azure Databricks account and a workspace set up. Secondly, you need to have Power BI Desktop installed on your machine, which you can download from the Microsoft website. Finally, you need to have the Power Query Editor enabled in your Power BI Desktop. To enable the Power Query Editor, go to File > Options and Settings > Options > Preview Features and select the checkbox for “New Get Data Experience”.
Connecting to Azure Databricks from Power BI
To connect to Azure Databricks from Power BI, we need to use the ODBC driver. The ODBC driver is a client library that allows Power BI to communicate with the Azure Databricks cluster. To download the ODBC driver, go to the Azure Databricks documentation website and download the appropriate driver for your system. Once you have downloaded and installed the driver, follow the steps below to connect to Azure Databricks from Power BI.
1. Open Power BI Desktop and go to the Home tab.
2. Click on “Get Data” and select “More” from the drop-down menu.
3. In the “Get Data” window, select “ODBC” and click on “Connect”.
4. In the “ODBC” window, enter the following information:
– Server: Enter the server name of your Azure Databricks cluster.
– Database: Enter the name of the database you want to connect to.
– Username: Enter your Azure Databricks username.
– Password: Enter your Azure Databricks password.
5. Click on “OK” to connect to Azure Databricks.
Using Power Query M Language Code to Connect to Azure Databricks
Once you have connected to Azure Databricks, you can use Power Query to transform and shape your data. To use Power Query, you need to write M Language code. M Language code is a functional programming language that is used to query and transform data in Power Query. In the next section, we will explore how to write M Language code to connect to Azure Databricks.
Step 1: Create a New Query
To create a new query, click on “New Source” in the Home tab and select “Blank Query”. This will open a new query window.
Step 2: Write M Language Code
In the new query window, click on “Advanced Editor” in the View tab. This will open the M Language editor. In the M Language editor, enter the following code to connect to Azure Databricks:
let
Source = Odbc.DataSource(“dsn=AzureDatabricks”, [
HierarchicalNavigation=true,
Pooled=true,
Query=”
select from table_name
where column_name = ‘value’
“
]),
#”Transform” = Source{[Schema=”default”,Item=”table_name”]}[Data]
in
#”Transform”
In the code above, replace `AzureDatabricks` with the name of the ODBC driver that you installed. This code sets up a connection to Azure Databricks and retrieves data from the table specified in the query.
Step 3: Load Data
Once you have written the M Language code, click on “Done” to close the M Language editor. Power Query will now load the data from Azure Databricks and display it in the query window. You can now transform and shape the data according to your needs.
Conclusion
In this article, we explored the Power Query M Language Code for Connecting to the Azure Databricks Data Source from Inside Power BI. We discussed the prerequisites for connecting to Azure Databricks from Power BI, the steps to connect to Azure Databricks, and how to write M Language code to retrieve data from Azure Databricks. By following these steps, you can easily connect to Azure Databricks from Power BI and retrieve the data you need to create powerful visualizations.
