Data Sources in Power BI

Power BI supports a broad spectrum of data sources and offers multiple connectors to integrate with these sources.

The primary type of data sources in Power BI can be categorized into:

Files – Excel, CSV, XML, JSON, etc.
Databases – SQL Server, Oracle, MySQL, PostgreSQL, etc.
Online Services – Azure, SharePoint, Google Analytics, Salesforce, etc.

1. File-Based Sources

Excel (.xlsx, .xlsm)
CSV (Comma-Separated Values)
JSON (JavaScript Object Notation)
XML (eXtensible Markup Language)
PDF (Portable Document Format)
SharePoint Folder

2. Database-Based Sources

SQL Server Database
Oracle Database
MySQL Database
PostgreSQL Database
IBM DB2
Amazon Redshift

3. Cloud-Based Sources

Azure SQL Database
Azure Data Lake Storage
Azure Blob Storage
Google BigQuery
Amazon S3

4. Online Sevices

SharePoint Online
Salesforce
Google Analytics
Dynamics 365
MailChimp

5. Other Sources

Web Data (using Web Connector)
OData Feeds
R Scripts
Python Scripts

To view the full list of supported connectors in Power BI Desktop, go to Home → Get Data → More.

Importing Data vs Direct Query

In Power BI, the connection methods available to connect with the data sources are:

Import,
DirectQuery.

Import Mode

In import mode, data is copied and stored in Power BI’s in-memory engine. It requires scheduled refresh to keep data up-to-date. This mode is suitable to handle Small to medium datasets where performance is critical.

Import mode can be used to perform complex data transformations. It provides faster response times for visuals and queries but is limited by memory capacity.

DirectQuery Mode

Each time a report is accessed in direct query mode, queries are sent directly to the source database. It is suitable for real-time data access and the performance is dependent on the source database’s performance.

Direct query mode is suitable for large datasets that require real-time data updates. It has limited data transformation capabilities and slow performance for complex queries.

Choosing Between Import and DirectQuery

Import Mode is suitable for manageable data sizes and fast, high-performance visuals.

DirectQuery can be used for real-time data and very large datasets that exceed memory limits.

Working with Excel, SQL Server, and Cloud Data (Azure, SharePoint, etc.)

The most commonly used data sources in Power BI are Excel, SQL Server, and cloud services like Azure and SharePoint.

Working with Excel Data

Load Excel Data:

Open Power BI Desktop.
Go to Home → Get Data → Excel.
Browse and select the Excel file.
Choose the worksheet(s) or table(s) to load.

Transform Data:

Click Transform Data to open Power Query Editor.
Perform cleaning and shaping tasks, like removing duplicates, changing data types.
Click Close & Apply to load data into Power BI.

Connecting to SQL Server

Load SQL Server Data:

Go to Home → Get Data → SQL Server.
Enter the Server name and Database.
Choose Import or DirectQuery.

Authenticate:

Use Power Query Editor for any necessary transformations.

Connecting to Cloud Data (Azure, SharePoint)

a) Azure SQL Database

Go to Home → Get Data → Azure → Azure SQL Database.
Enter the Server and Database details.
Authenticate with your Azure credentials.
Choose Import or DirectQuery based on your needs.

b) SharePoint Online

Go to Home → Get Data → Online Services → SharePoint Online List or SharePoint Folder.
Enter the SharePoint site URL.
Authenticate and select the list or folder to load.

Request more information

Data Sources in Power BI