Import Excel Data In Power BI

Introduction

 

In the previous article, I taught you how to import CSV in Power BI, but in this article you will learn how to create a report using Excel data. To import the Excel data into Power BI is quite similar to CSVs but today I will add more functionality and make my report very effective.

 

So, let’s start with importing data from an Excel file. You can get the sample Excel file in ‘Source Code’.

 

Step 1

 

First of all, we need to get the data from Excel sheet. So from the ‘Home’ page of the tool, click on ‘Get Data’ then select ‘Excel’ and click on ‘Connect’

 

 

Now browse the file path and select the file and open it.

 

 

After clicking on ‘Open’ select your sheet from navigator and then click on ‘Load’.

 

 

Now your file has been loaded.

 

Step 2

 

Now we will generate our reports.

 

We have Excel data with employee details with the below header

 

empno

ename

job

mgr

hiredate

sal

comm

deptno

 

So, for the first report we will have the task to show the employee's name, their designation and their salary in table format.

 

To accomplish this task we click on ‘Report’ and select ‘ename’, ‘job’ and ‘salary’ from the ‘Fields’ at the right side of the tool) also select the ‘Table’ format from ‘Visualizations’(just before ‘Fields’ tab)

 

 

Step 3

 

Now you can perform many tasks to make your report effective, such as,

1. You can sort your data like the below image:
   
   
   
   
2. Rename the column of your report. To rename the column click on Fields ‘Values’. Here we can see the three fields (ename, job and sal) that you selected to create the  report.
   
   
   

Right click on the field (or fields) that you want to rename, now you get the option of ‘Rename’. Click on ‘Rename’ and change your field name.

 

After renaming the fields, your report is shown like below:

3. You can change the format of your report like background color, border, font, text color etc. To do so, click on ‘Format’
   
   
   

Here you can get many options for formatting. I am using column headers to change the color of header part and change font from 10pt to 16pt.

 

Also using ‘Field formatting’ to change the background color of fields.

 

Step 4

 

Now that your first report has been created, you can easily save and share it with others. To save the file, go to ‘File’ tab and click on ‘save as’. Now your report has been saved with “.pbix” extension.

 

 

Conclusion

 

I hope now you are able to create a report using table visualization of an Excel sheet data. Try this and wait for my next article. Thanks for reading. Have a good day!!!