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.
Table of contents
- 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
- 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.
#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
- Google BigQuery
- Google BigQuery (Azure AD) (Beta)
- Actian (Beta)
- Amazon Athena
- AtScale cubes (Beta)
- BI Connector
- Data Virtuality LDW
- Dremio Software
- Dremio Cloud (Beta)
- InterSystems IRIS (Beta)
- Jethro (Beta)
- Linkar PICK Style/MultiValue Databases (Beta)
- 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)
- 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)
- FactSet Analytics
- Palantir Foundry
- Hexagon PPM Smart®API
- Industrial App Store
- Intune Data Warehouse (Beta)
- Planview ProjectPlace
- 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.
#6 – Other Category
Power BI can also connect to other data sources available under the Other category, which include:
- SharePoint List
- OData Feed
- Active Directory
- Microsoft Exchange
- Hadoop File (HDFS)
- Hive LLAP
- R Script
- Python script
- OLE DB
- Acterys: Model Automation & Planning (Beta)
- Amazon OpenSearch Service (Beta)
- Autodesk Construction Cloud (Beta)
- BitSight Security Ratings
- BQE Core
- Bloomberg Data and Analytics
- Cherwell (Beta)
- Cognite Data Fusion
- Delta Sharing
- Eduframe (Beta)
- EQuIS (Beta)
- FactSet RMS (Beta)
- Google Sheets
- Information Grid (Beta)
- Jamf Pro (Beta)
- MicroStrategy for Power BI
- OpenSearch Project (Beta)
- QubolePresto (Beta)
- Roamler (Beta)
- SIS-CC SDMX (Beta)
- Shortcuts Business Insights (Beta)
- SingleStore Direct Query Connector 1.0 (Beta)
- Socialbakers Metrics 1.1.0 (Beta)
- Starburst Enterprise
- SurveyMonkey (Beta)
- Microsoft Teams Personal Analytics (Beta)
- Tenforce (Smart)List (Beta)
- Usercube (Beta)
- 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.
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)
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:
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.
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.
This has been a guide to Power BI Connections. Here we discuss about the top 6 types of Power BI data source connections with examples. You can learn more from the following articles –