Azure Synapse Analytics workspace

Power BI is a popular business intelligence tool that allows users to connect to a wide range of data sources. One such data source is the Azure Synapse Analytics workspace, which is a powerful analytics service that can handle large amounts of data. In this article, we will explore how to connect to the Azure Synapse Analytics workspace data source from inside Power BI using Power Query M Language code.

Introduction to Power Query M Language

Power Query M Language is a powerful and flexible language that is used to transform and load data in Power BI. It is a functional language that allows users to write custom functions, create loops and conditionals, and manipulate data in a variety of ways. Power Query M Language is used in the Power Query Editor, which is a graphical user interface that allows users to transform and shape data before it is loaded into Power BI.

What is Azure Synapse Analytics workspace?

Azure Synapse Analytics workspace is a cloud-based analytics service that allows users to analyze large amounts of data from a variety of sources. It is designed to handle big data workloads and provides users with a scalable and flexible environment for data storage and processing. Azure Synapse Analytics workspace allows users to run complex queries and analytics on large datasets, and provides integration with other Azure services such as Azure Data Factory, Azure Databricks, and Power BI.

Connecting to Azure Synapse Analytics workspace Data Source from Inside Power BI

To connect to the Azure Synapse Analytics workspace data source from inside Power BI, we need to use Power Query M Language code. The following steps outline the process:

1. Open Power BI and navigate to the Power Query Editor.

2. Click on the “New Source” button and select “Azure” from the list of available data sources.

3. Select “Azure Synapse Analytics” from the list of available Azure services.

4. Enter your Azure Synapse Analytics workspace account details and click “Connect”.

5. Select the tables or views that you want to import into Power BI and click “Load”.

The above steps will create a connection to the Azure Synapse Analytics workspace data source and load the selected tables or views into Power BI. However, to customize the data import process and perform advanced data transformations, we need to use Power Query M Language code.

Power Query M Language Code for Connecting to Azure Synapse Analytics workspace

The following Power Query M Language code can be used to connect to the Azure Synapse Analytics workspace data source from inside Power BI:

let

Source = AzureSynapseAnalytics.Contents(““),

ResourcePath = “///“,

Query = “SELECT FROM ” & ResourcePath,

SynapseAnalytics = Source{[Name=”“]}[Data],

#”Changed Type” = Table.TransformColumnTypes(SynapseAnalytics,{{“Column1”, type text}, {“Column2”, Int64.Type}})

#”Changed Type”

The above code uses the AzureSynapseAnalytics.Contents() function to create a connection to the Azure Synapse Analytics workspace data source. The function takes the URL of the workspace as an argument. We then define the ResourcePath variable, which specifies the location of the table or view that we want to import. The Query variable is used to define the SQL query that we want to execute on the data source.

Finally, we use the SynapseAnalytics variable to access the data from the specified location and transform the data using the Table.TransformColumnTypes() function.

Conclusion

Connecting to the Azure Synapse Analytics workspace data source from inside Power BI using Power Query M Language code is a powerful way to customize the data import process and perform advanced data transformations. With the code provided in this article, you can easily connect to the Azure Synapse Analytics workspace data source and import the data that you need for your analysis.