Starburst Enterprise
Power BI is one of the most popular business intelligence tools used by companies today. It allows users to easily connect to various data sources, analyze data, and create insightful visualizations. One of the data sources that can be connected to Power BI is Starburst Enterprise, a powerful data analytics platform used by many organizations.
In this article, we will explore how to use Power Query M Language code to connect to the Starburst Enterprise data source from inside Power BI.
What is Power Query M Language?
Power Query is a data connection and transformation tool that is built into Power BI. It allows users to connect to various data sources, clean and transform data, and load the data into Power BI for analysis. Power Query uses a functional language called M to perform data transformation tasks.
M is a case-sensitive language that is used to write formulas and expressions in Power Query. It is a powerful language that allows users to create custom functions, perform data manipulation, and create advanced data transformation logic.
Connecting to Starburst Enterprise from Power BI
To connect to Starburst Enterprise from Power BI, we need to install the Starburst Enterprise ODBC driver on our local machine. Once the driver is installed, we can use Power Query M code to connect to the data source.
Here are the steps to connect to Starburst Enterprise from Power BI:
1. Open Power BI Desktop and click on the “Get Data” button on the Home tab.
2. Select “ODBC” as the data source and click on “Connect”.
3. In the “ODBC Connection” dialog box, select the Starburst Enterprise data source from the drop-down list.
4. Enter your Starburst Enterprise username and password in the “User ID” and “Password” fields.
5. Click on “Connect” to connect to the Starburst Enterprise data source.
6. In the “Navigator” pane, select the tables or views that you want to use in your analysis and click on “Load” to load the data into Power BI.
Writing Power Query M Language Code
Now that we have connected to the Starburst Enterprise data source from Power BI, we can use Power Query M Language code to transform and clean the data.
Here are some examples of Power Query M Language code that can be used to transform data:
Filtering Data
To filter data based on a specific condition, we can use the “Filter” function in Power Query M Language. For example, to filter data to only show sales data for a specific year, we can use the following code:
= Table.SelectRows(#”PreviousStep”, each [Year] = 2021)
Grouping Data
To group data by a specific column and perform an aggregate function, we can use the “Group By” function in Power Query M Language. For example, to group sales data by product category and calculate the total sales for each category, we can use the following code:
= Table.Group(#”PreviousStep”, {“ProductCategory”}, {{“TotalSales”, each List.Sum([Sales]), type number}})
Joining Data
To join two or more tables together, we can use the “Join” function in Power Query M Language. For example, to join sales data with product data based on a common column, we can use the following code:
= Table.Join(#”SalesData”, {“ProductID”}, #”ProductData”, {“ProductID”}, “ProductInfo”)
Conclusion
Power Query M Language is a powerful tool that can be used to connect to various data sources, clean and transform data, and load the data into Power BI for analysis. By using Power Query M Language code, we can easily perform advanced data transformation tasks to create insightful visualizations.
In this article, we explored how to use Power Query M Language code to connect to the Starburst Enterprise data source from inside Power BI. We also provided some examples of Power Query M Language code that can be used to transform data.
By following the steps outlined in this article, you can easily connect to the Starburst Enterprise data source from Power BI and start analyzing your data today!