Azure Synapse Analytics SQL
Power BI is a business analytics service by Microsoft that allows users to visualize and analyze data from a wide range of sources. One of the key features of Power BI is its ability to connect to various data sources, including Azure Synapse Analytics SQL data source. In this article, we will discuss how to connect to Azure Synapse Analytics SQL data source from inside Power BI using Power Query M language code.
What is Azure Synapse Analytics?
Azure Synapse Analytics is a cloud-based analytics service that provides a unified experience for data warehousing, big data, and real-time analytics. It allows users to run complex queries on petabyte-scale data using T-SQL and Apache Spark. With Azure Synapse Analytics, users can easily ingest, prepare, manage, and serve data for immediate business intelligence and machine learning needs.
What is Power Query M Language?
Power Query M Language is a functional programming language used to query and transform data in Power BI. It allows users to create custom functions and queries to extract, transform and load data from various sources. Power Query M Language is similar to Excel’s Power Query, which is used to transform data within Excel.
Connecting to Azure Synapse Analytics SQL Data Source from Inside Power BI
To connect to Azure Synapse Analytics SQL data source from inside Power BI, we need to use the Power Query Editor. Power Query Editor is a data transformation and cleansing tool that allows users to reshape and cleanse data before loading it into Power BI.
Step 1: Open Power Query Editor
To open Power Query Editor, click on the Edit Queries button in the Home tab of the Power BI ribbon.
Step 2: Connect to Azure Synapse Analytics SQL Data Source
To connect to Azure Synapse Analytics SQL data source, click on the New Source button in the Home tab of the Power Query Editor. From the list of available data sources, select the SQL Server option.
Step 3: Enter Connection Details
In the SQL Server database dialog box, enter the server name and database name for your Azure Synapse Analytics SQL data source. If you want to use Windows Authentication, select the option Use Windows Integrated Security. Otherwise, select the option Use a specific user name and password and enter the credentials.
Step 4: Write Power Query M Language Code
To write Power Query M Language code for connecting to Azure Synapse Analytics SQL data source, click on the Advanced Editor button in the View tab of the Power Query Editor. In the Advanced Editor, enter the following code:
let
Source = Sql.Database(“ServerName”, “DatabaseName”,
[Query=”SELECT FROM TableName”]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“ColumnName”, type text}})
in
#”Changed Type”
In the above code, replace “ServerName” with the name of your server, “DatabaseName” with the name of your database, “TableName” with the name of the table you want to connect to, and “ColumnName” with the name of the column you want to transform.
Step 5: Load Data into Power BI
To load data into Power BI, click on the Close & Apply button in the Home tab of the Power Query Editor. The data will be loaded into the Power BI data model, where you can start creating visualizations and reports.
Conclusion
Connecting to Azure Synapse Analytics SQL data source from inside Power BI using Power Query M Language code is a simple and effective way to extract and analyze data. With the ability to run complex queries on petabyte-scale data, Azure Synapse Analytics provides a powerful platform for business analytics and machine learning. By following the steps outlined in this article, you can quickly and easily connect to Azure Synapse Analytics SQL data source from inside Power BI and start visualizing and analyzing your data.
