How To Connect SQL Database With Power BI

by

In this blog, I have described how to connect to SQL database in Power BI desktop and create different visualizations based on the SQL database table. We can take any SQL server and use the data to create a report in Power BI desktop. To achieve this process, we need to go through some steps. First, we need to import data from SQL server into our Power BI Desktop, then we will go through the cleaning the data process phase with Power Query Editor and after that, we will create some visualizations in the canvas.

Connect to the SQL server database

I have described this process in other articles also. Now I am going to explain this process step by step. This process is easy to understand and easy to implement. If you follow all the steps then you can implement easily.

Step 1

First, click on Get Data as shown below.

Step 2

Click on More, and get new search box popup will open and within the search box enter SQL and click on SQL server database then Connect as shown below.

Step 3

It will open a dialog box. Now, you need to enter the server IP address, database and select import on data connectivity mode which you want to import in Power BI desktop. There are two processes basic and Advanced. In Basic, it will give you the complete SQL database data and in advanced you can specify the parts of the database and it will only provide that much data. In this step, we are going to paste this SQL server authentication details in the dialog box. After this, a navigator window will open which looks like this.

After authentication, you get the navigator window and select any database and select any data table. Now click on the load button.

Edit Data in Power Query Editor

Now, let’s start the second phase. Choose any table, I am going to choose Results [edit] and click Edit. It will open the table in Power Query Editor. Using this, we can remove, transpose, rename, change the data type, remove a row, remove a column, split the column, etc. The data looks like this.

You can edit in Power BI editor according to requirements. If you want to update data type of any column then Right click on the column and then show the list of all property. Now, you can change accordingly. You can rename the column name. Right now it is just showing the column 1 which is meaningless. To rename the column, follow the same procedure as above but instead of clicking ‘Remove’, you need to click ‘Rename’.

Now, you need to enter the value you want to replace and value Replace with. In our case first we will enter details and a second text box will remain as blank because we are replacing it with the null value.

Also, If you want to remove rows from the top rows, click on Remove rows and then remove Top Rows and then pop up will come in which you need to specify the total number of rows to be removed. One more thing I want to show you the right side of the Query Editor is Query Settings. Within Query Settings, there are two boxes, one is your Properties and second is your applying step.

In properties, there is a Name field. Within the name field, the table name is showing. You can edit the table name and also you can give some description too which is optional.
Second is the Applied Steps. This shows all the steps which we have applied like Changed type, Remove columns, Replace Value and Removed Top Rows. After all changes click on close and apply on the top left that is data imported successfully on Power BI.

In the last step, click on the close and apply on the top left corner on power BI editor and now dataset has been updated successfully. Now you can move on the visualization step.

Create a Visualization

Now, data has been imported on Power BI desktop. You can create a visual report for better visualization. I am going to create a waterfall chart. So, select the waterfall graph and drag the column SCITY and SST. This report looks like:

Conclusion

In this blog, I explained to you how you can connect to a SQL server database. Now you can manipulate the data using query editor. If you want to know how to create a complete report in Power BI, I have written a complete blog on that particular topic. You can check it there. Also, I have written many blogs on Power BI. I just want to know your response. If you have any query or facing any issue you can write in the comment section too. Thanks!

Latest posts by Ravi Verma (see all)

Leave a Comment