Introduction
In the previous article, we discussed 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 refer to the previous article to get a 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 let us click Home from the Ribbon Tool, and choose the 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 you want 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 worksheets, then each of the sheets 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 it into the Power BI.
Click now to let us choose the Load option and we will discuss 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 a 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 a 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 into 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 the 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.
Click Create Table, a new window will be prompted and enter the data as we do in the Excel sheet. We can create columns and rows to enter the data.
Fig. 13 Create Table Window
The remaining is similar to what we did for Excel and SQL Server Data sources. 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 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 reading other articles in this series
Join the conversation! Your thoughts help the community grow.