EClytics: Business Intelligence Services
How Can We Help?
< All Topics

How to load data in Power BI?

Importing Data in Power BI desktop

Every large organization usually has huge volumes of data. Eventually, managing that much data is always a task. Whereas, to take out useful conclusions from that data is even tougher. But, when you’re dealing with data from multiple sources, it becomes a very daunting task. Power BI can be easily connected to a variety of sources to import data for reporting. Data can either be imported from its source or Direct Query is used to connect data.

To import data go to > Home tab > Get Data > more > select Data source > Connect as follows:

The various data sources are:

  • Files: Excel Workbook, Text/CSV, XML, JSON, Folder, PDF, Parquet, Share point folder.
  • Database: SQL Server Database, Oracle database, IBM database, SAP HANA database, etc.
  • Azure: Azure SQL database, Synapse Analytics SQL, Azure Blob storage, Cosmos DB, etc.
  • Online services:  Dynamics 365, Dynamics NAV, Salesforce object, GitHub (Beta), etc.
  • Others:  Web, OData Feed, Hadoop File, Spark, Python script, Anaplan Connector, etc.

Import data from Files:

There are following types of files that can be connected to power BI to import data:

  1. MS Excel (.xlsx or .xlsm):
  2. Power BI connects with Excel 2007 and later versions to import data.
  3. Excel workbook can also be connected to external source and get the data imported from that source to power BI.
  • Comma separated Value (.csv):
  • CSV files also called comma separated value are simple files having data in the form of rows and each value is separated by a comma.
  • The csv files can store large amount of data in a relatively small file. This file has an extension .csv

Import Data from Folder

Let us consider three files which are there in a folder named state wise sales. The three files having cities of three different states i.e. Haryana, Uttar Pradesh and Maharashtra are as follows:

All the three files have same columns and data types but the only difference is the name of the rows. To import data we go to the ‘Home’ tab> Get data> more> Folder. The steps are shown as follows:

After that connect with the folder i.e. State_wise_sales having all three files as follows:

The files are read as follows:

Here we have only two options either combine and load or combine and transform data. We do combine and transform. The files are appended in the power Query Editor:

Here we have another column that specify the files name from where the rows have been imported. This column can be removed by Remove column options given in power Query after that, Close and Apply. The data is ready to make reports.

Data Load from server to Power BI

Data that is analyzed in the Power BI can be imported from variety of tools including data server such as Microsoft SQL server, MYSQL, etc.

  • Data Load from Microsoft SQL server: To import data from Microsoft SQL server, we have a dummy data base in our server i.e. Student Database. The table inside it is details:

In power BI, go to the Get Data option. Here the Power BI shows options of sever database as follows:

Server name and database name are required. Press OK.  Then it will be connected with the server.

Here data can be imported in two ways:

Import: If Import is used then only the image of the data will be imported. Any change in the data after importing will not get affected in Power BI.

DirectQuery:  In this data will be up to data and any change in the data in server will change the data in the Power BI.

Here click on StudentData> ‘Details’. The preview will be shown at the right side. Click ‘Load’ or ‘Transform’ depending upon the requirement.

Data Load from MYSQL:

Here we create a dummy data in MYSQL. In power BI go to > Get data > More > MySQL database. Now connect it with the Power BI.

Connect with the MySQL server, give server name and then database name. Click on OK.

The Studentdata is connected to the Power BI. It will show the preview. Then Click on Load or transform depending upon the requirement.

Add comment

Table of Contents