Connect Power BI With Different Data Sources

Introduction

In the previous article, we discussed about the different data sources that can provide data for Power BI. In this article we will learn how to connect those data sources with Power BI. I recommend you to refer the previous article to get better understanding.

As mentioned before, in this article we will learn, How to connect Power BI with,

  • Excel
  • SQL Server Database
  • Entering Data Directly into Power BI

How to Connect Power BI With Excel

To connect Power BI with Excel, first lets click Home from the Ribbon Tool, and choose Get Data menu option.

Fig.1 Connecting Power BI with Excel Sheet

Go to File in the Get Data window, and choose Excel and click on Connect as shown in the above figure. Browse the Excel file and upload the data. Power BI will start connecting to that data file. We have just connected to our data source, and Power BI does not load the data. So do not move the file from the source location.

Choose which worksheet’s data wanted to be loaded and we can see the data preview on the right side of the screen as shown below.

Fig. 2 Loading Data from Excel sheet

If we have more worksheet, then each of the sheet will be loaded individually.

Fig. 3 Three Options at the Bottom

If we look at the bottom of the window, we can see three options as Load, Transform Data and Cancel.

  • Load – Load is used to load the data as it is.
  • Transform Data – It is used to modify our data before loading into the Power BI.

Click now lets choose the Load option and we will discuss about the Transform Data later by. We can see the data is loaded from the excel sheet from the below image.

Fig. 4 Data being loaded from the Excel

The loaded workbook is located under the Fields option.

Fig. 5 Loaded Workbook under Fields

When we expand the Sheet1, all the data will be shown.

Fig. 6 All Data

To create headers for the report, all we have to do is choose the data from the tile and within few seconds our report will be ready.

Fig. 7 Simple Report Build using Excel Sheet

We can change the visualization of the report from the Visualizations options, that is, we can change the report format.

How to Connect Power BI with SQL Server

We can connect to SQL Server Database in few clicks, all we need is the proper credentials. As we did in the previous step, go to the Get Data Option from the Home menu in the Ribbon tool. Choose SQL Server as the option.

Fig.8 Connecting Power BI with SQL Server

Enter the Server name and database name to connect

Fig. 9 Server Name to connect

Click Ok, and in the next window all the Databases available under that server will be shown. Choose the database and the tables to load the data.

Fig. 10 Listing the Databases

Now the data is being loaded in to the Power BI report as similar as did for the Excel Sheet.

Fig. 11 Data is Being loaded from Database to Power BI

Choose the required headers from the Fields option and customize the report from Visualization option and our report is ready.

Fig. 12 Report prepared from the SQL Server Table

How to Load Data Directly into Power BI

We can load or enter data directly into the Power BI report. From the Ribbon tool, choose Home and click Enter Data option.

Create Table window will be prompted and enter the data as we do in Excel sheet. We can create columns and rows to enter the data.

Fig. 13 Create Table Window

Remaining is as similar as what we did for Excel and SQL Server Data source. We have to choose the headers from the Field option and prepare the report.

Fig. 14 Simple Report Prepared By Entering Data Directly into Power BI

Conclusion

In this article, we have discussed about the various data sources connectivity in Power BI. I hope you all found this article much useful. We will discuss more concepts in Power BI in our upcoming articles. Please share your feedback in the comment section.

Consider to read other articles in this series

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: