Power BI turns data into insightful reports. It is a data visualization and business intelligence platform offered as a part of the Microsoft Power Platform.
The platform takes data reporting and data analysis to the next level with interactive reports and immersive visuals. The data integration possibilities are impressive, with a wide range of in-built apps and connectors.
But what data sources can you connect to Power BI? Let’s find out.
In this post, we’ll explore the different types of Power BI data sources and the methods to add a new data source.
What are Power BI data sources?
In Power BI, a data source is the entity from which the data is extracted for creating reports and visualizations. It can be a file, webpage, database, or other app/service.
Power BI supports local as well as cloud data sources.
This means you can connect Power BI to data sources in the same computer/server. And you can also access data sources stored in other servers (cloud) and extract the required data.
Data sources are vital elements of Power BI reports. They provide the raw material for creating reports and visualizations.
Data sources in Power BI: Desktop vs Service
Microsoft Power BI is available in two editions:
- Power BI Desktop
- Power BI Service
There is a greater diversity of data sources for Power BI Desktop. It features built-in apps and connectors to integrate data. Further, you use PowerQuery to get data from various sources and create datasets for visualization.
On the other hand, there are limited data sources in Power BI Service.
In Power BI Service, the data sources are limited to:
- OneLake data hub: You can access existing data and reports you’ve access to.
- Files: You can add files like Excel workbooks (.xlsx & .xlsm), Comma-separated values (.csv), and Power BI Desktop reports (.pbix).
- Cloud Databases: You can connect Azure databases like ASQL Database, Azure Synapse Analytics, and Spark in Azure HDInsight.
Power BI Desktop is a recommended choice for creating dashboards and reports, which can be later published to Power BI Service. You can also set data auto-refresh to keep the reports updated.
Microsoft Power BI: Types of data sources supported
Power BI Desktop can connect to data from a wide range of sources.
To get the list, simply go to the Home tab and click Get data.
In the dropdown, you can find the most commonly used data sources. Click More… at the bottom to get the list of all the data sources.
(You can proceed to connect to different data sources by clicking the Connect button)
Power BI Desktop supports the following types of data sources:
- Flat Files
- Microsoft Fabric
- Power Platform
- Online Services
In addition, you can also connect to data sources from other categories. For example, Google Sheets.
Preview, Beta, and Legacy data sources in Power BI
Microsoft Power BI keeps releasing support for new data sources in Power BI. And you may find some Power BI data sources are marked with Beta, Preview, or Legacy.
Beta and Preview are the early versions with limited support and functionality and should not be used in production environments. You may not be able to use these early versions of data sources in Power BI service until they’re made generally available.
The Legacy data sources are outdated versions. They should be avoided as well.
Microsoft Power BI data sources list
Here are the data sources you can connect to in Power BI.
Power BI: File data sources
In Power BI, you can directly import data from flat files.
It supports the following types of files:
- Excel Workbook: Tables in Excel file (.xlsx & .xlsm)
- Text/CSV: Unstructured text files and .csv files
- JSON: Structured data stored in JSON files
- Folder: Metadata and links of files in a folder
- PDF: Data in tabular format in PDF files
- SharePoint folder: Metadata and links of files in the SharePoint folder
These types of data sources are ideal for importing static as well as dynamic files.
To connect, go to Get data > More… > File, select the preferred file, and click Connect.
Here, Microsoft Excel is a powerful source to extend the integration capabilities. For example, you can use Power Query (Get & transform data) in Excel to create dynamic worksheets with data from various sources and connect it to Power BI for visualization.
Power BI: Database data sources
You can connect Power BI to many popular data sources and import data for building reports.
Power BI supports the following databases:
- 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
- Google BigQuery
- Google BigQuery (Azure AD)(Beta)
- Actian (Beta)
- Amazon Athena
- AtScale cubes
- BI Connector
- Data Virtuality LDW
- Dremio Software
- Dremio Cloud
- InterSystems IRIS (Beta)
- Jethro (Beta)
- Linkar PICK Style / MultiValue Databases (Beta)
- MongoDB Atlas SQL (Beta)
- TIBCO® Data Virtualization
To connect, go to Get data > More… > Database, select the preferred database, and click Connect.
Some of these connectors may require additional components (database clients.) For example, you’ll need to install MySQL Connector/NET to connect to a MySQL database.
You may need to enter credentials, like username and password, to authenticate the connection. And you can choose the data connection type from:
- Import: This will take a snapshot and data when loading and store it. All filters and interactions will be performed on the cache version.
- DirectQuery: This will query the database at the time of run. Here, you can filter or interact with the database itself.
The direct query method is suitable for very large and dynamic datasets. Especially when the freshness of data is important.
Note: After setup, you can not switch a data source from import to direct query.
Power BI: Microsoft Fabric
Microsoft Fabric is a complete suite of business intelligence and analytics tools by Microsoft, including the Power BI Service.
The data sources include:
- Power BI datasets: Data from datasets that your account has access to
- Dataflows: Collection of tables in your Power BI Service account (a.k.a Dataflow Gen2)
- Datamarts*: Specialized databases from the Power BI Premium account
- Warehouses*: Data from an integrated warehouse in Microsoft Fabric
- Lakehouses*: Data from an integrated lakehouse in Microsoft Fabric
- KQL Databases*: Data from KQL databases in Microsoft Fabric
The sources marked with * are offered in the Preview version (meaning it is not generally available yet).
To connect, go to Get data > More… > Microsoft Fabric, select the preferred source, and click Connect.
Power BI: Power Platform data sources
Power Platform is a product group by Microsoft to build apps, automate processes, and analyze data.
You can connect the following Power Platform sources to Power BI:
- Power BI dataflows: Data from dataflows in your workspace. It is a legacy connector.
- Common Data Service: Data stored in the Microsoft cloud database. It is a legacy connector and has been superseded by Dataverse.
- Dataverse: Data from multiple stores integrated via Dataverse
- Dataflows: Collection of tables in the Power Platform
These data sources are suitable for organizations dealing with large datasets and custom apps built with the Power Platform.
To connect, go to Get data > More… > Power Platform, select the preferred source, and click Connect.
Power BI: Azure data sources
You can use the following data sources from Microsoft Azure in Power BI:
- 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, go to Get data > More… > Azure, select the preferred service, and click Connect.
Power BI: Online Services data sources
Power BI offers native connectors for many online services and apps.
You can import data from:
- 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)
- 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)
- 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
- Product Insights (Beta)
- Profisee (Beta)
- SoftOne BI (Beta)
- Planview IdeaPlace
- TeamDesk (Beta)
- Webtrends Analytics (Beta)
- Witivio (Beta)
- Viva Insights
- Zoho Creator
To connect, go to Get data > More… > Online Services, select the preferred service, and click Connect.
Power BI: Template apps data sources
Power BI template apps are pre-built connectors that integrate third-party sources and generate instant reports.
Power BI Premium or Pro users can install the Power BI Apps from the marketplace. To explore the apps, navigate to Get data dropdown and click Power BI Template Apps.
Here, you can find various apps built by Power BI Partners.
Explore more at Power BI Apps marketplace.
Example of how to connect data sources to Power BI using the native connector
Power BI desktop offers a diverse range of data sources. And before we explore the supported data source, let’s first understand how to connect one.
In this example, we’ll connect the following Excel spreadsheet to Power BI and visualize its data. It contains sales data of an online fashion store.
To get started, open Power BI desktop and create a report.
In the Home tab, click Get data and select Excel workbook in the dropdown.
In the file explorer, browse to the file’s directory and select the Excel spreadsheet. Click Open to load data from the file to Power BI.
The Navigator popup will appear.
Select the worksheet(s) and click Load to load the data into Power BI. In this step, you can also choose to transform the data in Power Query Editor before loading. But it’s optional.
Now, wait for the Power BI to load the data. This may take a bit, depending on the file size.
Once the process is completed, you can find the Excel spreadsheet as one of the data sources. And apply in-built visualizations to the columns.
This was a simple example of using native data connectors in Power BI.
How to connect other data sources to Power BI?
Microsoft Power BI offers comprehensive support for data sources. And this can be extended further.
Let’s say you want to pull a list of products from Shopify to Power BI but there is no native connector for Shopify in Power BI.
You can connect other (not natively supported) data sources to Power BI using:
- Third-party connector: No-code platforms like Coupler.io can connect more data sources to Power BI and automate reporting.
- ODBC connection: Leverage the native ODBC support in Power BI to connect to additional data sources. Requires installing additional ODBC components and setup.
- Python program: Code a custom Python script to get data from your preferred source into Power BI. This requires coding expertise.
Let’s explore how you can connect data sources to Power BI using Coupler.io.
Connect data sources to Power BI using Coupler.io
Coupler.io is a data automation and analytics platform. You can use it to connect various data sources to Power BI and automate the integration. Coupler.io supports 50+ data sources, including CRM apps, accounting software, advertising platforms, and so on.
The main advantage of this connection method is that you can automate data refresh on a custom schedule. As you know, you can’t schedule data refresh on the Power BI desktop. But with Coupler.io, you can implement this.
Let’s see how you can use Coupler.io to connect Shopify to Power BI,
First, create a free Coupler.io account. Log into your account and create a new importer. Select Shopify in the Source and Power BI in the Destination. Click Proceed to create the importer.
Step1. Extract data from Shopify
In the account, enter your Shopify store name in the following format – storename.myshopify.com. Log into your Shopify account and authenticate the request (you only have to do this for the first setup).
In Data entity, choose the data you want to extract from Shopify. For example, Products.
Click Finish and Proceed.
Optionally, you can also continue to configure advanced settings. For example, you can filter the entries by date, product status, etc.
Now, the data sources added to the importer will be displayed. Here you can choose to connect more data sources by repeating the above steps.
Click Transform Data to proceed further.
Step 2. Transform Data
Coupler.io will display a preview of data extracted from Shopify. You can also transform this data before unloading it into Power BI. You can perform transformations like:
- Sort and filter data
- Hide specific columns
- Create new calculated columns
Let’s say you want to hide unwanted columns from the data. You can simply do this by unselecting those columns in Column management.
Once you’re done, click Proceed on the top right corner.
Step 3. Manage data to Power BI
Now, Coupler.io will generate a Power BI integration URL. You can use this URL to import the data into Power BI. Copy the integration URL as shown below.
Now, proceed further and Save and run the importer.
Switch to Power BI Desktop and open a report. In the Home tab, open the Get data dropdown and click Web.
Paste the importer URL in the URL field and click OK.
Now, the Power BI will pull the data from Shopify using the Coupler.io importer. You can edit the data in the Power Query editor before loading. Click Close & Apply in the top left corner to load the data.
This will load the data into Power BI. And you can use it to create charts and other visualizations, like the one shown below.
You can also set up automatic data refresh for the importer in Coupler.io.
Simply select the respective importer from the dashboard. Switch the Automatic data refresh on and set the following:
- Interval: Select how often you want to refresh the data.
- Days of week: Select the days of the week for the data refresh.
- Timer preferences: Set the time preference to run the imports.
- Schedule time zone: Select the applicable time zone for the time preference.
Click Update Schedule to save the settings.
Now, Coupler.io will automatically update the importer at set intervals and keep you updated with the real-time data in Power BI.
That’s how easily Coupler.io can connect and automate data sources to Power BI.
Microsoft Power BI data sources – Frequently asked questions
Can Power BI combine data from multiple sources?
Yes, you can combine data from multiple sources in Power BI. You can do this by merging or appending multiple queries in the Power Query Editor or by simply adding multiple sources when setting up a Coupler.io importer. We’ve blogged how you can do this in the example of Looker Studio as a destination, but the same logic applies to Power BI. Check out how to connect multiple data sources with Coupler.io
How to fix: Power BI isn’t refreshing all data sources
Power BI may fail a data refresh if there is a change in data schema (meaning column/table names or data types.) To fix this, you need to refresh the data schema in Power BI Desktop and republish the report to Power BI Service.
The better option is to use Coupler.io, which will automate your data refresh on a schedule and release you from this headache.
Why are data source credentials greyed out in Power BI?
In Power BI, greyed-out credentials mean you don’t have permission to edit them or there is a data connection issue. It can be due to a misconfiguration or a wrong data type selected.
What are the free data sources in Power BI?
All Power BI native connectors are free to use. However, some of them are only supported in the paid plan. Popular native connectors available in the free version are Microsoft Excel, CSV, Web, JSON, etc.
What are Power BI DirectQuery supported data sources?
Some popular DirectQuery-supported data sources in Power BI are:
- Azure SQL database
- SQL Server
- Teradata database
- Azure Synapse Analytics (SQL DW)
- SAP HANA database
- Amazon Athena
- IBM Db2 database
- Oracle database
- Google Analytics
- Adobe Analytics
What are Power BI cloud data sources?
Power BI cloud sources are the sources present in other computers/servers. Examples include Azure data sources, Google Analytics, SQL Server, etc.
Explore data sources for Power BI yourself
The number of Microsoft Power BI data sources is overwhelming. You can connect flat files, cloud databases, online services, and more.
Sometimes, it can be challenging to find the right connector for your data analysis and reporting needs. And sometimes, setting up the native connector can be complex, too.
A better way is to use platforms like Coupler.io to easily integrate a wide range of data sources and automate the integration. Sign up now!
Eager to learn more? Check our article on Power BI dashboard examples.
Streamline your data analytics & reporting with Coupler.io!
Coupler.io is an all-in-one data analytics and automation platform designed to close the gap between getting data and using its full potential. Gather, transform, understand, and act on data to make better decisions and drive your business forward!
- Save hours of your time on data analytics by integrating business applications with data warehouses, data visualization tools, or spreadsheets. Enjoy 200+ available integrations!
- Preview, transform, and filter your data before sending it to the destination. Get excited about how easy data analytics can be.
- Access data that is always up to date by enabling refreshing data on a schedule as often as every 15 minutes.
- Visualize your data by loading it to BI tools or exporting it directly to Looker Studio. Making data-driven decisions has never been easier.
- Easily track and improve your business metrics by creating live dashboards on your own or with the help of our experts.
Try Coupler.io today at no cost with a 14-day free trial (no credit card required), and join 700,000+ happy users to accelerate growth with data-driven decisions.Start 14-day free trial