Power BI – Import Excel Data

In this blog, you will gain an understanding of the role of Import Mode in Power BI Desktop and how to acquire and load data into Power BI Desktop using an Excel file.

Primarily, Power BI Desktop supports two types of data source connection modes: Import and Direct Query. This discussion will focus on Import mode.

In Import mode, Power BI Desktop stores the data inside the Power BI Cache. If your data size is less than 1 GB or if the data does not undergo continuous changes, Import mode is recommended.

Import mode is known for its speed compared to Direct Query mode because all data is sourced from the Power BI Desktop Cache. Therefore, it is an excellent choice for development and testing purposes.

When you choose import mode, Power BI fetches the data from the given source and loads it into the Power BI cache. This is a one-time activity. Subsequently, you can schedule or refresh to obtain the latest data.

Let’s get started:

In this section, we will load Excel data, which includes three different sheets: Order, Returns, and Users.

Follow these steps to import data from Excel to Power BI Desktop-

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

Step-2: After that, it will display the names of your sheets. Here, you can select the sheets you want to load into Power BI, and then click on the ‘Load’ button.

Step-3: After clicking on ‘Load,’ it loads all the data into the Power BI data cache.

After the compilation process, you will see three datasets under the Fields tab. Power BI converts your sheets into datasets, with columns represented as fields.

Power BI is intelligent, it automatically assigns data types to your fields based on your data’s behavior.

For example, in the ‘Returns’ dataset, Power BI automatically changed the data type of ‘Order Id’ to Integer.

You can see the sum Icon before Order Id, indicating that you can perform aggregations on this field. Additionally, you have the option to manually change the data type in Power BI Desktop.

The import mode in Power BI offers several benefits, which make it the preferred choice for most scenarios.

Here are some of the key advantages of using the import mode in Power BI:

1. High Performance: Importing data into Power BI provides the best performance. Data is loaded into Power BI’s internal VertiPaq engine, which compresses and optimizes it for fast querying and visualization.
2. Offline Availability: Once data is imported, Power BI reports remain fully functional even when you’re offline or disconnected from the data source. This is crucial for users who need to access reports without a live data connection.
3. Complex Calculations: Import mode allows you to create complex calculated columns and measures using DAX (Data Analysis Expressions). These calculations can significantly enhance the insights you can derive from your data.
4. Data Transformation: Power Query in Power BI Desktop enables data transformation and cleansing before data is imported. You can reshape, filter, and clean data as needed.
5. Data Security: Import mode allows you to apply row-level security, ensuring that users only see data they are authorized to access.
6. Aggregation and Summary: You can pre-aggregate data in the import mode to improve query performance and reduce the load on the source database.
7. Large Dataset Support: Power BI Pro and Premium users can handle large datasets in the import mode, taking advantage of memory optimization and performance enhancements.
8. Custom Data Models: Import mode allows you to create custom data models that combine data from multiple sources and perform advanced data modeling.
9. Enhanced Visuals: Power BI’s visualizations, like Matrix and Chart visuals, work best with data in the import mode, providing interactive and dynamic reports.
10. Reduced Dependency: Importing data reduces the dependency on the live data source, preventing issues caused by changes or outages in the source system.

It’s important to note that the choice between import and other connectivity modes (Direct Query, Live Connection) depends on your specific use case, data source, and requirements.

Import mode is ideal when you need high performance, complex calculations, and offline access, but it may require periodic data refreshes to keep the data up to date.

Similar Post

• Power BI Import Mode with SQL Data
• Power BI – Direct Query Mode

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.

Recommended Post:

Power BI – Get Web Page Data

Stacked Column Chart in Power BI

Power BI – Stacked Bar Chart