Power BI Data Sources: Different Types & How to Connect Them

Power BI Data Sources encompass the various connections that can be established to create data flows within the Power BI environment.

Widely adopted within organizations, Power BI stands as a key tool for analyzing business data and generating comprehensive reports. With Power BI, users can aggregate, analyze, visualize, and collaborate on data.

Continue reading to discover detailed information about Power BI Data Sources, including an exploration of the types and the procedures for adding new data sources.

In this article:

What are Power BI data sources?

Microsoft Power BI is a business analytics service that provides interactive visualizations and business intelligence capabilities with an interface simple enough for end users to create their own reports and dashboards.

With Power BI, you can connect to, transform, and visualize data from a wide range of sources, including Excel, BigQuery, SQL Server, and cloud-based services such as Azure SQL Database and Salesforce. You can also use Power BI to create custom dashboards and reports that can be shared with your team or organization.

In Power BI, a data source refers to the entity from which data is extracted to generate reports and visualizations. This source can be a file, webpage, database, or another application or service.

Power BI accommodates both local and cloud-based data sources, allowing connections to sources on the same computer/server as well as those stored in other servers (cloud). These connections enable the extraction of necessary data.

Microsoft Power BI is available in two editions:

1. Power BI Desktop

- Offers a diverse range of data sources, featuring built-in apps and connectors for data integration.
- Utilizes PowerQuery to fetch data from various sources and create datasets for visualization.

2. Power BI Service

- Presents limited data sources compared to Power BI Desktop.
- Available data sources include the OneLake data hub for accessing existing data and reports, files such as Excel workbooks (.xlsx & .xlsm), Comma-separated values (.csv), and Power BI Desktop reports (.pbix).
- Supports connections to cloud databases like Azure SQL Database, Azure Synapse Analytics, and Spark in Azure HDInsight.

In Power BI Service, users can access existing data and reports through the OneLake data hub, add various file types, and connect to cloud databases such as Azure. Power BI Desktop is recommended for creating dashboards and reports, which can then be published to Power BI Service. Additionally, data auto-refresh can be set up to ensure that reports remain up-to-date.

How to connect to a Power BI Data Source?

The steps to acquire data from any Power BI data source supported by Power BI Desktop are as follows:

Step 1: Navigate to the “Home” ribbon.

Step 2: Click on the “Get Data” button label or the down arrow.

Step 3: From the list of Common data sources, choose the “More” option at the bottom.

Image source

Step 4: The “Get Data” dialog box appears. Select the desired Power BI data source.

Image Source

Step 5: Click on the “Connect” button. In the image below, the ‘Web’ option is chosen from the ‘Other’ data connection category.

Image Source

Step 6: A connection window specific to the data type is displayed. If credentials are required, you will be prompted to enter them. The image shows entering a URL to connect to a Web data source.

Image Source

Step 7: Enter the URL or resource connection information, then click “OK.” Power BI Desktop connects to the data source and displays available sources in the Navigator.

Image Source

Step 8: Choose the “Load” button at the bottom of the “Navigator” pane to load the data. Select the “Transform Data” button to edit the query in Power Query Editor before loading the data.

Types of Power BI Data Sources

For Power BI Service

There are two approaches to create or import data in the Power BI service.

1) Files

Excel (.xlsx, .xlsm):
Power BI Desktop (.pbix)

Comma-Separated Value (.csv)

This method involves using Get & Transform to query, transform, and load data into an Excel or Power BI Desktop file initially, especially for other file types like XML Table (.xml) or text (.txt) files.

The Excel or Power BI Desktop file can be brought into Power BI.

2) Databases

Within the Power BI service, users have the capability to establish live connections to certain cloud-based databases:

Azure SQL Database
Spark on Azure HDInsight
Azure Synapse Analytics (previously SQL Data Warehouse)

The connections from Power BI to these databases operate in real-time. For instance, if you connect to an Azure SQL Database, and proceed to analyze its data by crafting reports in Power BI, any data slicing or addition of a new field to a visualization triggers a database query executed by Power BI.

However, for other types of databases within your organization, you would be required to connect to, query, and load data into a data model using Power BI Desktop or Excel.

For Power BI Desktop

The Power BI Desktop supports the following categories of Power BI data sources:

Power BI Data Sources: All
Power BI Data Sources: File
Power BI Data Sources: Database
Power BI Data Sources: Power Platform
Power BI Data Sources: Azure
Power BI Data Sources: Online Services
Power BI Data Sources: Other
Power BI Data Sources: Template Apps

1) All

In this classification, you can discover all the Power BI data sources accessible in Power BI Desktop.

The image below illustrates the Get Data window for All data sources.

2) File

In the File category, you will find the Power BI data sources listed below.

Under the File category, you can find the following Power BI Data sources.

Excel Workbook
Text/CSV
XML
JSON
Folder
PDF
Parquet
SharePoint folder

The image below displays the Get Data window for File data sources.

Upon selecting the ‘File’ option, the Power BI desktop will present the various supported flat file types. Choose the file type from the list and click the ‘Connect’ button, ensuring you specify the file’s location.

3) Database

The image below provides a view of the Get Data window for Database data sources.

In the Database category, you will encounter the following Power BI data sources.

SQL Server database
Access database
SQL Server Analysis Services database
Oracle database
IBM Db2 database
IBM Informix database (Beta)
IBM Netezza
MySQL database
PostgreSQL database
Sybase database
Teradata database
SAP HANA database
SAP Business Warehouse Application Server
SAP Business Warehouse Message Server
Amazon Redshift
Impala
Google BigQuery
Vertica
Snowflake
Essbase
AtScale cubes
Actian (Beta)
Amazon Athena
BI Connector
Data Virtuality LDW
Denodo
Dremio Software
Dremio Cloud (Beta)
Exasol
Indexima
InterSystems IRIS (Beta)
Jethro (Beta)
Kyligence
Linkar PICK Style / MultiValue Databases (Beta)
MariaDB
MarkLogic
MongoDB Atlas SQL (Beta)
TIBCO(R) Data Virtualization

When you click the “Database” option, it will show you all the available database connections supported by the Power BI desktop. Select the database type from the list and press the “Connect” button. You must specify the file’s location.

To connect, you must enter the Server name/User name and password. Using the Advance options, you can also connect via a direct SQL query. You can also choose between Import and DirectQuery connectivity modes.

Upon selecting the ‘Database’ option, the Power BI desktop will display the available database connections. Choose the database type from the list and click the ‘Connect’ button, specifying the file’s location.

To establish the connection, you need to input the Server name/User name and password. Advanced options allow connection through a direct SQL query.

You can choose between Import and DirectQuery connectivity modes.

4) Microsoft Fabric (Preview)

To establish a connection, navigate to Get data > More… > Microsoft Fabric, choose the desired source, and click Connect.”

The image below provides a view of the Get Data window for Microsoft Fabric data sources.

The available data sources encompass:

Power BI datasets: Data sourced from datasets accessible to your account.
Dataflows: A collection of tables within your Power BI Service account, also known as Dataflow Gen2.
Datamarts*: Specialized databases accessible through a Power BI Premium account.
Warehouses*: Data extracted from an integrated warehouse within Microsoft Fabric.
Lakehouses*: Data obtained from an integrated lakehouse within Microsoft Fabric.
KQL Databases*: Data sourced from KQL databases within Microsoft Fabric.

Please note that sources marked with * are currently in the Preview version, indicating they are not yet generally available.

5) Power Platform

The image below provides a view of the Get Data window for Power Platform data sources.

In the Power Platform category, you will discover the following Power BI data sources:

Power BI dataflows
Common Data Service (Legacy)
Dataverse
Dataflows

6) Azure

The image below illustrates the Get Data window for Azure data sources.

Establishing a connection to an Azure cloud database is possible through the Azure option.

In the Azure category, you will encounter the following Power BI data sources.

Azure SQL Database
Azure Synapse Analytics SQL
Azure Analysis Services database
Azure Database for PostgreSQL
Azure Blob Storage
Azure Table Storage
Azure Cosmos DB
Azure Data Explorer (Kusto)
Azure Data Lake Storage Gen2
Azure Data Lake Storage Gen1
Azure HDInsight (HDFS)
Azure HDInsight Spark
HDInsight Interactive Query
Azure Synapse Analytics workspace (Beta)
Azure Time Series Insights (Beta)
Azure Cost Management
Azure Databricks

7) Online Services

The image below provides a glimpse of the Get Data window for Online Services data sources.

Power BI offers the capability to connect with various online services, including Exchange, Salesforce, Google Analytics, and Facebook.

Within the Online Services category, you will find the following Power BI data sources:

SharePoint Online List
Microsoft Exchange Online
Dynamics 365 Online (legacy)
Dynamics 365 (Dataverse)
Dynamics NAV
Dynamics 365 Business Central
Dynamics 365 Business Central (on-premises)
Azure DevOps (Boards only)
Azure DevOps Server (Boards only)
Salesforce Objects
Salesforce Reports
Google Analytics
Adobe Analytics
appFigures (Beta)
Data.World – Get Dataset (Beta)
GitHub (Beta)
LinkedIn Sales Navigator (Beta)
Marketo (Beta)
Mixpanel (Beta)
Planview Enterprise One – PRM (Beta)
QuickBooks Online (Beta)
Smartsheet
SparkPost (Beta)
SweetIQ (Beta)
Planview Enterprise Architecture
Zendesk (Beta)
Asana (Beta)
Assemble Views
Autodesk Construction Cloud
Automation Anywhere
Automy Data Analytics (Beta)
CData Connect Cloud
Dynamics 365 Customer Insights (Beta)
Databricks
Digital Construction Works Insights
Emigo Data Source
Entersoft Business Suite (Beta)
eWay-CRM
FactSet Analytics
Palantir Foundry
Funnel
Hexagon PPM Smart® API
Industrial App Store
Intune Data Warehouse (Beta)
Planview Projectplace
Product Insights (Beta)
Profisee (Beta)
Quickbase
SoftOne BI (Beta)
Planview IdeaPlace
TeamDesk (Beta)
Webtrends Analytics (Beta)
Witivio (Beta)
Viva Insights
Zoho Creator

8) Other

The image below illustrates the Get Data window for Other data sources.

In the ‘Other’ category, you will discover the following Power BI data sources:

Web
SharePoint list
OData Feed
Active Directory
Microsoft Exchange
Hadoop File (HDFS)
Spark
Hive LLAP
R script
Python script
ODBC
OLE DB
Acterys: Model Automation & Planning (Beta)
Anaplan Connector v1.0 (Beta)
Solver
Bloomberg Data and Analytics
Cherwell (Beta)
Cognite Data Fusion
Delta Sharing
EQuIS (Beta)
FHIR
Google Sheets (Beta)
Information Grid (Beta)
Jamf Pro (Beta)
Kognitwin
MicroStrategy for Power BI
Paxata
QubolePresto (Beta)
Roamler (Beta)
SIS-CC SDMX (Beta)
Shortcuts Business Insights (Beta)
Siteimprove
SumTotal
SurveyMonkey (Beta)
Microsoft Teams Personal Analytics (Beta)
Tenforce (Smart)List
Usercube (Beta)
Vena
Vessel Insight
Zucchetti HR Infinity (Beta)
BQE Core
MicroStrategy for Power BI
Starburst Enterprise
Amazon OpenSearch Service (Beta)
OpenSearch Project (Beta)
Blank Query

9) Template apps

Power BI provides template apps that contain dashboards, data models, embedded queries, datasets, etc. Users can directly use a collection of elements in the Template apps.

By clicking on the Template Apps link located towards the bottom of the Get Data window, you can access template apps tailored for your organization.

How to Establish Connections with Non-Native Data Sources in Power BI?

Microsoft Power BI offers robust support for a variety of data sources, and this capability can be expanded even further. For instance, if you wish to extract a list of products from Hubspot to Power BI but encounter the absence of a native connector for Hubspot in Power BI, there are alternative methods for establishing connections with non-native data sources:

Third-Party Connector

Platforms like Windsor.ai, a no-code solution, can easily link additional data sources to Power BI and automate the reporting process.

ODBC Connection

Utilize the inherent ODBC support in Power BI to connect to diverse data sources. This involves the installation of additional ODBC components and setup.

Python Programming

Develop a customized Python script to fetch data from your preferred source into Power BI. This method requires coding expertise.

Connecting Data Sources to Power BI with Windsor.ai

Let’s delve into the process of connecting data sources to Power BI using Windsor.ai.

Windsor.ai is a versatile data automation and analytics platform facilitating the connection of various data sources to Power BI, along with automation capabilities. Supporting over 75 data sources, including CRM apps, and advertising platforms, Windsor.ai provides a broad range of compatibility.

Let’s walk through the process of using Windsor.ai to connect Hubspot to Power BI:

That’s how easily Windsor.ai can connect and automate data sources to Power BI.

Try Windsor.ai today

Access all your data from your favorite sources in one place.
Get started for free with a 30 - day trial.

Start Free Trial

Summary

In this article, you have gained insights into Power BI Data Sources. The content also covered details about Power BI, connecting to Power BI Data Sources, and the various types of such sources.

Windsor.ai, a No-code Data Pipeline, offers a seamless and dependable solution for efficiently managing data transfers across diverse sources and a broad spectrum of desired destinations, all achievable with just a few clicks.

Check out some of the features of Windsor.ai:

G2 Review score average 4.8 out of 5
ETL (Extract – Transform – Load) functionality for all data destinations (incl. databases) available on all plans
All connectors available on all plans: No premium connectors
Extensive library of marketing dashboard templates
Database destination pricing independent of row count
SOC 2 Type 2 certified, supports single sign on and multi factor authentication

FAQ

Can Power BI Consolidate Data from Multiple Sources?

Yes, Power BI allows you to consolidate data from multiple sources.

What Are Power BI Cloud Data Sources?

Power BI cloud sources refer to sources located on other computers/servers. Examples include Azure data sources, Google Analytics, SQL Server, and more.

Which Data Sources Support Power BI DirectQuery?

Several popular data sources support DirectQuery in Power BI, including Azure SQL Database, PostgreSQL, SQL Server, Dataverse, Teradata Database, Azure Synapse Analytics (SQL DW), SAP HANA Database, SingleStore, Amazon Athena, IBM Db2 Database, Oracle Database, Denodo, Snowflake, Google Analytics, Adobe Analytics, and ODBC.

What Are the Free Data Sources in Power BI?

All Power BI native connectors are free to use, although some are only supported in the paid plan. Common native connectors available in the free version include Microsoft Excel, CSV, Web, JSON, and more.