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.
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.
If we have more worksheet, then each of the sheet will be loaded individually.
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.
The loaded workbook is located under the Fields option.
When we expand the Sheet1, all the data will be shown.
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.
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.
Enter the Server name and database 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.
Now the data is being loaded in to the Power BI report as similar as did for the Excel Sheet.
Choose the required headers from the Fields option and customize the report from Visualization option and our report is ready.
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.
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.
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.
Learn With Sundar 