Databricks
Power BI is a powerful data visualization and reporting tool that allows businesses to make informed decisions based on data insights. Power BI’s capabilities are further enhanced with Power Query, a data transformation and cleansing tool. In this article, we will focus on the Power Query’s M Language code for connecting to the Databricks data source from inside Power BI.
Databricks is a cloud-based big data analytics platform that enables data processing, machine learning, and collaboration across various teams. Power BI can be used to connect to Databricks and create insightful reports and dashboards. Let’s dive into the M Language code for connecting to Databricks from Power BI.
Prerequisites
Before we start with the M Language code, there are a few prerequisites that need to be fulfilled. You should have an active Databricks account, and the Databricks cluster should be running. You also need to have Power BI Desktop installed on your system.
Step-by-Step Guide
1. Open Power BI Desktop and go to the ‘Home’ tab.
2. Click on the ‘Transform Data’ button to open the Power Query Editor.
3. In the Power Query Editor, click on the ‘New Source’ button, and select ‘Other’ from the drop-down menu.
4. Select ‘Databricks‘ from the list of available data sources.
5. In the ‘Databricks Connector’ window, enter the URL of your Databricks workspace, and select the authentication method.
6. If you choose the ‘OAuth2’ authentication method, you need to enter your Databricks workspace credentials.
7. Click on the ‘Connect’ button to establish a connection with Databricks.
8. Once connected, you can see the Databricks data source in the ‘Navigator’ pane.
9. Click on the Databricks data source to view the available data tables.
10. Select the desired table and click on the ‘Edit’ button to open the Power Query Editor.
11. In the Power Query Editor, you can apply transformations to the data as required.
12. Once done with the transformations, click on the ‘Close & Apply’ button to save the changes.
M Language Code for Connecting to Databricks
The above steps provide a graphical approach to connect to Databricks from Power BI. However, Power Query also supports M Language code for connecting to various data sources, including Databricks. Here is the M Language code for connecting to Databricks from Power BI:
let
Source = Databricks.Data(“{URL}”),
#”DatabaseName” = Source{[Name=”{DatabaseName}”]}[Data],
#”TableName” = #”DatabaseName”{[Name=”{TableName}”]}[Data]
in
#”TableName”
To use the above code, replace “{URL}”, “{DatabaseName}”, and “{TableName}” with the respective values for your Databricks data source. The code creates a connection to Databricks and selects the specified table to extract the data.
Conclusion
In conclusion, Power BI’s Power Query feature allows for easy and seamless connection to various data sources, including Databricks. By following the simple steps outlined in this article and using the provided M Language code, you can establish a connection between Power BI and Databricks and create insightful reports and dashboards.
