Skip to main content
Power BIPowerBI tutorials

Connect Power BI to SQL Server

Power BIPowerBI tutorials

Connect Power BI to SQL Server

Let's take a step-by-step approach to connecting Power BI to SQL Server database. In addition to this Connect Power BI to SQL Server, how to use SQL queries with the data present in SQL tables with an example

Step-by-step approach to connecting Power BI to SQL Server

If you haven't started Power BI Desktop yet, double-click Power BI Desktop to open it. Once it's open, it looks like the following screenshot. Click on the Get information Hyperlink

connect-power-bi-to-sql-server-1-1617761

If you are on the Power BI visualization page, on the Home tab, click the Get information option and select the SQL server, as we show below. Or select More options.

connect-power-bi-to-sql-server-2-2688683

We are selecting More options to show you the following window. Use this window to select the desired data source. Here, we are talking about how to connect Power BI to SQL Server. So, let me select the SQL Server database Option.

connect-power-bi-to-sql-server-3-9567225

Connect Power BI to SQL Server

The following is the list of fields available to fill in to connect Power BI to the SQL Server database

  • Server: Provide the name of the SQL Server instance. If you installed SQL with the default instance, the instance name is the computer name or localhost.
  • Database: This is optional. If you want to use a custom SQL query, it is required.
  • Data connectivity mode: Select whether you want to import or query directly.
connect-power-bi-to-sql-server-4-9525796

Clicking the Advanced option displays the advanced properties.

connect-power-bi-to-sql-server-5-1161003

You can write your own SQL query to extract data from the specified database. To do this, write your query inside the SQL Statement box

connect-power-bi-to-sql-server-15-4508087

The custom query we use in the Connect Power BI to SQL Server screenshot above is:

- Connecting Power BI to SQL Server SELECT Geo.EnglishCountryRegionName AS Country, Geo.StateProvinceName AS State, SUM (Fact.SalesAmount) AS Sales, SUM (Fact.TaxAmt) AS Tax, COUNT (Fact.OrderQuantity) AS Orders FROM FactResellerSales AS Fact INNER JOIN DimSalesTerritory ON Fact.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey INNER JOIN DimGeography AS Geo ON DimSalesTerritory.SalesTerritoryKey = Geo.SalesTerritoryKey GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName HAVING COUNT (Fact.Order00Quantity) 600

If you want to use this data, click Upload. For now, let me click the cancel button because I want to select multiple tables.

Maybe you are interested >>>  Stacked column and line chart in Power BI
connect-power-bi-to-sql-server-16-9158394

NOTE: Don't worry over the query, just understand the procedure. However, if you want to learn, check out our SQL Server Tutorial

Let me use the local instance and click OK

connect-power-bi-to-sql-server-6-1334529

For now, we are using the current local Windows credentials. If you do not have permission to access SQL Server, use someone else's Windows credential by selecting Use alternate credentials.

connect-power-bi-to-sql-server-7-1564063

In real time, we only use the credentials from the SQL Server database. For this, select the database and enter the username and password provided by the Admin person.

For now, I am using current Windows credentials.

connect-power-bi-to-sql-server-8-1714051

In the display options, it lists the databases that are available for that instance. In this example, we are selecting the AdventureWorksDW2017 database.

You can download this database from Microsoft's Official Download Center or GitHub for free.

connect-power-bi-to-sql-server-9-4677047

Expanding the Database folder displays the available tables and views. When you select the table, the Data Preview is displayed.

connect-power-bi-to-sql-server-10-9719125

In the following screenshot, you can see that we are selecting the required tables. Remember, if you are confused to select tables, select a table of Measures (Orders or Sales) and click Select related tables button.

  • Load: This will load data from the selected tables into Power BI. Since I know the data is clean, let me click the Upload button
  • Edit: Click this button to modify the columns or clean the data
connect-power-bi-to-sql-server-11-5116469

Please wait until the upload is complete

connect-power-bi-to-sql-server-12-8351415

Now you can see the tables we selected from the SQL Server database in the Fields section.

connect-power-bi-to-sql-server-13-5176174

Expand any table to see the columns (fields) within that table.

connect-power-bi-to-sql-server-14-9382611

TIP: This is the dataset that we will use for most of the Power BI tutorial.

Let's take a step-by-step approach to connecting Power BI to SQL Server database. In addition to this Connect Power BI to SQL Server, how to use SQL queries with the data present in SQL tables with an example

Step-by-step approach to connecting Power BI to SQL Server

If you haven't started Power BI Desktop yet, double-click Power BI Desktop to open it. Once it's open, it looks like the following screenshot. Click on the Get information Hyperlink

connect-power-bi-to-sql-server-1-1617761

If you are on the Power BI visualization page, on the Home tab, click the Get information option and select the SQL server, as we show below. Or select More options.

connect-power-bi-to-sql-server-2-2688683

We are selecting More options to show you the following window. Use this window to select the desired data source. Here, we are talking about how to connect Power BI to SQL Server. So, let me select the SQL Server database Option.

connect-power-bi-to-sql-server-3-9567225

Connect Power BI to SQL Server

The following is the list of fields available to fill in to connect Power BI to the SQL Server database

  • Server: Provide the name of the SQL Server instance. If you installed SQL with the default instance, the instance name is the computer name or localhost.
  • Database: This is optional. If you want to use a custom SQL query, it is required.
  • Data connectivity mode: Select whether you want to import or query directly.
connect-power-bi-to-sql-server-4-9525796

Clicking the Advanced option displays the advanced properties.

connect-power-bi-to-sql-server-5-1161003

You can write your own SQL query to extract data from the specified database. To do this, write your query inside the SQL Statement box

connect-power-bi-to-sql-server-15-4508087

The custom query we use in the Connect Power BI to SQL Server screenshot above is:

- Connecting Power BI to SQL Server SELECT Geo.EnglishCountryRegionName AS Country, Geo.StateProvinceName AS State, SUM (Fact.SalesAmount) AS Sales, SUM (Fact.TaxAmt) AS Tax, COUNT (Fact.OrderQuantity) AS Orders FROM FactResellerSales AS Fact INNER JOIN DimSalesTerritory ON Fact.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey INNER JOIN DimGeography AS Geo ON DimSalesTerritory.SalesTerritoryKey = Geo.SalesTerritoryKey GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName HAVING COUNT (Fact.Order00Quantity) 600

If you want to use this data, click Upload. For now, let me click the cancel button because I want to select multiple tables.

Maybe you are interested >>>  Format the Power BI line and clustered column chart
connect-power-bi-to-sql-server-16-9158394

NOTE: Don't worry over the query, just understand the procedure. However, if you want to learn, check out our SQL Server Tutorial

Let me use the local instance and click OK

connect-power-bi-to-sql-server-6-1334529

For now, we are using the current local Windows credentials. If you do not have permission to access SQL Server, use someone else's Windows credential by selecting Use alternate credentials.

connect-power-bi-to-sql-server-7-1564063

In real time, we only use the credentials from the SQL Server database. For this, select the database and enter the username and password provided by the Admin person.

For now, I am using current Windows credentials.

connect-power-bi-to-sql-server-8-1714051

In the display options, it lists the databases that are available for that instance. In this example, we are selecting the AdventureWorksDW2017 database.

You can download this database from Microsoft's Official Download Center or GitHub for free.

connect-power-bi-to-sql-server-9-4677047

Expanding the Database folder displays the available tables and views. When you select the table, the Data Preview is displayed.

connect-power-bi-to-sql-server-10-9719125

In the following screenshot, you can see that we are selecting the required tables. Remember, if you are confused to select tables, select a table of Measures (Orders or Sales) and click Select related tables button.

  • Load: This will load data from the selected tables into Power BI. Since I know the data is clean, let me click the Upload button
  • Edit: Click this button to modify the columns or clean the data
connect-power-bi-to-sql-server-11-5116469

Please wait until the upload is complete

connect-power-bi-to-sql-server-12-8351415

Now you can see the tables we selected from the SQL Server database in the Fields section.

connect-power-bi-to-sql-server-13-5176174

Expand any table to see the columns (fields) within that table.

connect-power-bi-to-sql-server-14-8235973

TIP: This is the dataset that we will use for most of the Power BI tutorial.

error: Attention: Protected content.