Power BI - Import Excel Data

In this blog you will understand role of Import Mode in Power BI Desktop & how you will get & load data into Power BI desktop using Excel file.

Mainly, Power BI Desktop support two types of data source connection mode like Import & Direct Query mode. Here we will focus on Import mode.

In this mode Power BI Desktop store the data inside Power BI Cache. If your data size is less then 1 GB or data not continually changing then you can use Import mode.

And it is very fast compare to Direct Query mode because all data comes from Power BI Desktop Cache, so you can use this mode to develop & testing purpose.

But later on you have to change your data source mode as a Direct Query for production, it will be depends on your organization you want to change this or not.

When you choose import mode, in that case Power Bi fetch the data from given source and load into Power BI cache, this is one time activity. Later on you can Schedule or refresh to get latest Data.

Let’s get started-

Here, we will load excel data and under this excel sheet we have three different sheets like Order, Returns & Users.

Import Mode in Power BI- Sample Data

Follow these steps to get data from excel to Power BI desktop-

Step-1: Go to Home tab > Click on Get data > Select Excel.

Get data from Excel into Power BI

Step-2: After that it will show your sheets name, here you can choose sheets which you want to load in Power BI and then click on Load button.

Import Mode in Power Bi- Load Tables

Step-3: After click on load it loads all data in Power BI data cache.

Import Mode in Power Bi- Data Loading

After process compilation, you will see three Data sets under Fields Tab. Power BI converts your sheets as a Data set and columns as a Fields.

Power BI is very smart, it assigns Datatypes of your fields automatically as per your data behavior.

Like for “Returns” dataset Power BI changed Data type of “Order Id” automatically as a Integer.

You can see Sum Icon before Order Id, that’s mean you can perform aggregation on this field. Also you can change the Datatype manually from Power BI desktop.