SQL Server database
Power BI is a powerful data visualization tool that can help businesses make informed decisions by analyzing and presenting data in a user-friendly way. One of the key features of Power BI is the ability to connect to various data sources, including SQL Server databases. In this article, we will explore the Power Query M Language code for connecting to SQL Server databases from within Power BI.
Overview of Power Query M Language
Power Query is a data transformation and shaping tool that is integrated into Power BI. It allows users to connect to various data sources, transform and shape the data, and load it into Power BI for analysis and reporting. Power Query uses a functional programming language called M to perform data transformations. M is a case-sensitive language that is designed to be extensible, which means that users can write custom functions and expressions in M to perform specific data transformations.
Connecting to SQL Server database using Power Query M Language
To connect to a SQL Server database from within Power BI, we need to use the Power Query Editor. The Power Query Editor can be accessed by selecting the Transform Data button on the Home tab of the Power BI Desktop ribbon. Once we are in the Power Query Editor, we can use the following M code to connect to a SQL Server database:
let
Source = Sql.Database(“servername”, “databasename”, [Query=”select from tablename”]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“column1”, type text}, {“column2”, type number}})
in
#”Changed Type”
In this code, we first define a variable called Source and use the Sql.Database() function to connect to the SQL Server database. The Sql.Database() function takes three parameters: the server name, the database name, and an optional SQL query that can be used to filter the data. In this example, we are selecting all columns from a table called tablename.
After defining the Source variable, we use the Table.TransformColumnTypes() function to change the data types of specific columns. This step is optional and can be omitted if the data types are already correct.
Once we have written the M code, we can click the Close & Apply button on the Home tab of the Power Query Editor to load the data into Power BI.
Additional Options for Connecting to SQL Server database
In addition to using the Sql.Database() function to connect to a SQL Server database, there are a few other options available in Power Query:
Using Windows Authentication
To connect to a SQL Server database using Windows Authentication, we can use the following M code:
let
Source = Sql.Database(“servername”, “databasename”, [Query=”select from tablename”], [CreateNavigationProperties=false, Encrypt=true, TrustServerCertificate=true])
in
Source
In this code, we have added three additional parameters to the Sql.Database() function: CreateNavigationProperties=false, Encrypt=true, and TrustServerCertificate=true. These parameters are used to enable encryption and trust the server certificate when using Windows Authentication.
Using SQL Server Authentication
To connect to a SQL Server database using SQL Server Authentication, we can use the following M code:
let
Source = Sql.Database(“servername”, “databasename”, [Query=”select from tablename”], [CreateNavigationProperties=false, Encrypt=true, TrustServerCertificate=true, Username=”username”, Password=”password”])
in
Source
In this code, we have added two additional parameters to the Sql.Database() function: Username=”username” and Password=”password”. These parameters are used to specify the SQL Server Authentication credentials.
Conclusion
In conclusion, Power Query M Language is a powerful tool for connecting to SQL Server databases from within Power BI. By using the Sql.Database() function and customizing the parameters, we can connect to SQL Server databases using Windows Authentication or SQL Server Authentication. The ability to connect to various data sources and perform complex data transformations makes Power BI a valuable tool for businesses of all sizes.
