Power BI Connections

What Are The Connections In Power BI?

Connections in Power BI refer to the process of establishing a link between Power BI and various data sources. These data sources can be files, databases, cloud-based data, etc. Power BI offers various connection types to connect to different data sources and perform subsequent data analysis or visualization. Some of the key Power BI connections types include:

Import – It allows users to import data into Power BI’s internal model for further processing
DirectQuery – It directly queries the data source instead of importing, thereby providing a real-time dataset
Live Connection – It allows querying the dataset on demand without importing or caching the dataset in Power BI

We will cover the data connections in detail in the subsequent sections below.

What are the Connections in Power BI?

Power BI is a powerful business intelligence and data visualization tool developed by Microsoft. It is available as a cloud-based service, a desktop application, and a mobile app.
Power BI Desktop is a Windows application with a user-friendly interface for connecting to data sources, creating visualizations, and designing reports.
You can create visualizations in Power BI Desktop by dragging and dropping fields onto the report canvas and selecting the type of visualization you want to use from the visualization pane.
Power BI can help you gain insights from your data, monitor KPIs and metrics, create interactive dashboards and reports, collaborate with others, and automate data refreshes and updates.

Top 6 Data Source Categories for Power BI?

Power BI supports multiple data sources for data analysis and visualization. All the data source categories available are visible under the Get data option in the Home tab. The section below will discuss the top 6 data source categories and the associated connection types.

#1 – File Category

The data connections available under File Category include:

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

To connect to any of the above data connections, select the File option from the list as shown below screenshot and click on Connect.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

#2 – Database Category

The entire list of data connections available Under the Database category includes:

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
Impala
Google BigQuery
Google BigQuery (Azure AD) (Beta)
Vertica
Snowflake
Essbase
Actian (Beta)
Amazon Athena
AtScale cubes (Beta)
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
TIBCO(R) Data Virtualization

To connect to any of the above data connections, select the Database option from the list as shown below screenshot and click on Connect.

#3 – Power Platform

Currently, Power BI supports only 4 data connections available under the Power Platform category, which include:

Power BI datasets
Datamarts (Preview)
Power BI dataflows (Legacy)
Common Data Service (Legacy)

To connect to any of the above data connections, select the Power Platform option from the list as shown below screenshot and click on Connect.

#4 – Azure Category

Power BI supports various cloud-based data connections available under Azure Category which include:

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

To connect to any of the above data connections, select the Azure option from the list as shown below screenshot and click on Connect.

#5 – Online Services Category

You can connect with various online-based data connections using Power BI connections and they are available under the Online Services category which includes:

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 Portfolios
QuickBooks Online (Beta)
Smartsheet
SparkPost (Beta)
SweetIQ (Beta)
Planview Enterprise Architecture
Zendesk (Beta)
Asana (Beta)
Assemble Views
Automation Anywhere
Automy Data Analytics (Beta)
CData Connect Cloud
Dynamics 365 Customer Insights (Beta)
Databricks (Beta)
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
Profisee
Quickbase
SoftOne BI (Beta)
Planview IdeaPlace
TeamDesk (Beta)
Webtrends Analytics (Beta)
Witvio (Beta)
Viva Insights
Zoho Creator

To connect to any of the above data connections, select the Online Services option from the list as shown below screenshot and click on Connect.

Power BI Connections - Online services.jpg

#6 – Other Category

Power BI can also connect to other data sources available under the Other category, which include:

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)
Amazon OpenSearch Service (Beta)
Anaplan
Autodesk Construction Cloud (Beta)
Solver
BitSight Security Ratings
BQE Core
Bloomberg Data and Analytics
Cherwell (Beta)
Cognite Data Fusion
Delta Sharing
Eduframe (Beta)
EQuIS (Beta)
FactSet RMS (Beta)
FHIR
Google Sheets
Information Grid (Beta)
Jamf Pro (Beta)
Kognitwin
MicroStrategy for Power BI
OpenSearch Project (Beta)
Paxata
QubolePresto (Beta)
Roamler (Beta)
SIS-CC SDMX (Beta)
Shortcuts Business Insights (Beta)
SingleStore Direct Query Connector 1.0 (Beta)
Siteimprove
Socialbakers Metrics 1.1.0 (Beta)
Starburst Enterprise
SumTotal
SurveyMonkey (Beta)
Microsoft Teams Personal Analytics (Beta)
Tenforce (Smart)List (Beta)
Usercube (Beta)
Vena
Vessel Insight
Zucchetti HR Infinity (Beta)
Blank Query

To connect to any of the above data connections, select the Other option from the list as shown below screenshot and click on Connect.

How to Connect Data in Power BI from the Web?

You can use the “Web” option in the “Get Data” menu to connect data in Power BI from the web. This option lets you specify a URL that returns data in a supported format, such as JSON or XML.

Let us understand this with the help of an example where we will extract the dataset from a web portal using a URL link.

URL link: https://www.forbes.com/lists/global2000/?sh=2b21465a5ac0 (Forbes Global 2000 list)

Step 1: Open Power BI Desktop, navigate to the Home tab, select the Get data dropdown option, select Web available in Other Category and click on Connect button.

Power BI Desktop – Home – Get data – Other – Web – Connect

Step 2: Select the Basic option, provide the URL link, and click on the OK button

A navigator pop-up page will be displayed with all the available display options in the URL link.

Power BI Connections - web - Step 2 - Navigator

Step 3: Choose the table you want to connect. Power BI will show you the preview of the selected table.

Step 4: Click on the Load button. Power BI will load the data.

Post-data upload, the table structure will be visible under the Data pane.

The underlying data will also be visible under the Data view pane.

Important Things To Note

Some important things to note when working with Power BI connections include:

The data source must be accessible from the network where Power BI is running.
The data source must be compatible with the version of Power BI being used.
Credentials are required to connect to many data sources and must be stored securely.
Changes to the data source schema may require updates to Power BI reports and visualizations.

Frequently Asked Questions (FAQs)

1. What is the difference between a direct query and a live Power BI connection?

Both Direct Query and Live Connection are two different connection types for connecting to data sources in Power BI. Below are the differences between these two categories:

2. How many types of power BI connections are there?

Power BI supports various types of data connections, including:

• Import: Data is imported into Power BI and stored in its internal data model. This data can be refreshed at regular intervals or manually as needed.
• DirectQuery: Here, data remains in the source system and Power BI sends queries to it to retrieve the real-time data as the report is being viewed.

• Live Connection: This connection allows Power BI to connect to a pre-existing Analysis Services model or Power BI dataset already created and deployed on a server.

• Power BI Data flows: This Power BI connections allows users to connect to dataflows created in Power BI and stored in the Power Platform Common Data Service.
• Power Platform Dataflows: This connection is part of the Microsoft Power Platform, which includes Power BI, Power Apps, and Power Automate. These dataflows can be used to create datasets that can be used across the Power Platform.

• Other Data Sources: Power BI also supports connections to various other data sources, including Excel files, CSV files, SharePoint lists, and more

The choice of connection type will depend on the specific needs and requirements of the report or dashboard.

3. Why are Power BI Connections not working?

There could be several reasons why Power BI connections are not working. Here are some possible causes and solutions:

• Incorrect credentials: Power BI connections may not work if the credentials entered are incorrect or have expired.
• Firewall issues: Power BI connections will fail to connect if the data source is behind a firewall blocking it to access the data source. Make sure the necessary ports are open for communication.
• Data source compatibility: Power BI may not be compatible with the version of the data source being used.
• Data source availability: Ensure that the data source is available and accessible from the network where Power BI is running. Check if the data source is up and running and if there are no network issues.
• Server down: Check if the server hosting the data source or the Power BI service is down or experiencing issues.
• Query issues: If there are issues with the query, such as syntax errors or unsupported data types, Power BI may not be able to retrieve the data. Check the query and fix any issues.
• Version conflicts: Power BI connections may not work if there are version conflicts with the data source or other components of the system. Ensure that all components are up-to-date and compatible.

Power BI Connections

What Are The Connections In Power BI?

Table of contents

Key Takeaways