HOW TO IMPORT DATA FROM EXCEL INTO POWER BI

INTRODUCTION

POWER BI is a data analysis tool [ Software] which can help us to analyze the data in the lightening speed.

CLICK HERE TO LEARN THE BASICS OF POWER BI

In our previous introductory post, we learnt the basics of the POWER BI environment. We also learnt that we can bring the data into the POWER BI from a large number of resources and analyze the data. Technically bringing the data into the POWER BI becomes the first step towards the analysis.

So, in this article, we will talk about the various ways and options of fetching the data into the POWER BI environment for the analysis.

We can utilize the data in POWER BI using the two main processes

In this article we would learn to import data from EXCEL into power bi.



PROCEDURE OF IMPORTING DATA FROM EXCEL INTO POWER BI

Let us create one visual using the MICROSOFT EXCEL.

We’ll do this using a standard procedure of importing the data into power bi.

It is also the solution of getting the data from the EXCEL to the POWER BI.


CREATING THE CONNECTION TO THE DATA SOURCE:

The first step is to create a connection to the data source.

For the example, we will take the data source as an EXCEL FILE, which is easily available for all the readers.

ALWAYS CREATE A TABLE IN EXCEL IF WE INTEND TO USE THE DATA IN POWER BI.

Let us create an excel file containing the following table. [The table can be copied easily]

SALES DATA
WEEKASIAAMERICAEUROPEAUSTRALIAAFRICA
WEEK 12332245667
WEEK 23234345456
WEEK 37997558177
WEEK 49484845482
WEEK 58180947352
WEEK 65489979883
WEEK 77798905578
DATA PRESENT IN EXCEL FILE


STEPS TO CREATE CONNECTION IN POWER BI:

  • Click the POWER BI icon to execute the file and open POWER BI FOR DESKTOP.
  • To create a connection with the Excel file, click on one of the following options.
  • CLICK Excel directly from the DATA OPTIONS or go to GET DATA and choose EXCEL.
CHOOSE EXCEL DATA SOURCE

Just by clicking on the EXCEL OPTION, FILE OPENING DIALOG BOX, will open directly.

  • Select the file, which we want to get the data from, and click open.
  • After the file has been selected, POWER BI will scan the file for all the data and the following screen will follow.

 ONCE THE DATA COMES TO THE NAVIGATOR, THE CONNECTION IS FORMED. 

The NAVIGATOR gives the option for the data selection.

Power bi will scan our EXCEL FILE and enlist the complete data which can be imported.Let us understand the navigator.

On the left side are the Sheets.

We select the sheet and on the right pane, it’ll show the data present in the sheet.

We have only one table which is being shown. If more than one table, under the SHEET OPTION, the table options will appear which we can select and checkout the data before we import it. 

For loading the data, select the table or tables by checking the checkbox on the left pane and click LOAD [If the data is ready and clean] or TRANSFORM DATA [If the data needs cleaning] through POWER QUERY.

If we click TRANSFORM DATA, it’ll open in POWER QUERY. 

  • For our example, the data is clean so we click LOAD.

FOR THE SERVICES OPTIONS, THE SERVICES WOULD ASK FOR AUTHENTICATION WITH YOUR USERNAME AND PASSWORD. ONCE DONE, IT’LL CREATE THE CONNECTION WITH THAT SERVICE AND FETCH THE DATA AVAILABLE.

ONCE THE DATA COMES TO THE NAVIGATOR, THE CONNECTION IS FORMED.

CREATING THE CONNECTION TO THE DATA SOURCE:

  The NAVIGATOR gives the option for the data selection.

Power bi will scan our EXCEL FILE and enlist the complete data which can be imported.Let us understand the navigator.

On the left side are the Sheets.

We select the sheet and on the right pane, it’ll show the data present in the sheet.

We have only one table which is being shown. If more than one table, under the SHEET OPTION, the table options will appear which we can select and checkout the data before we import it.

For loading the data, select the table or tables by checking the checkbox on the left pane and click LOAD [If the data is ready and clean] or TRANSFORM DATA [If the data needs cleaning] through POWER QUERY.

If we click TRANSFORM DATA, it’ll open in POWER QUERY. 

  • For our example, the data is clean so we click LOAD.
CHOOSE THE TABLE TO BE LOADED INTO POWER BI

 After we click load, the data would be imported into POWER BI and the table would enter the POWER BI as the fields in the right portion of the screen as shown in the picture below.

 The fields have been populated in the POWER BI. Now we can choose the fields and create any visuals.

Let us choose the sales data of Asia.For choosing the sales data of Asia we need to check COLUMN1 and ASIA [The names of the column can be changed easily using RIGHT CLICK> EDIT QUERY ]

 Choose the desired visual.

Let us create the clustered visual.

The location of the clustered column chart is shown in the picture below.

FIRST VISUAL IN POWER BI IS READY.

THE PROCEDURE WE JUST DISCUSSED IS THE STANDARD PROCEDURE OF CREATING A CONNECTION AND ADDING OR IMPORTING DATA TO POWER BI.