Want to turn data into insightful reports? We all do. Power BI is a great tool for this job. This data visualization and business intelligence platform is offered as a part of the Microsoft Power Platform and it allows you to create interactive analyst-friendly reports. Its data integration capabilities are impressive as well, with a wide range of in-built apps and connectors.
In this post, we explore the different types of Power BI data sources and the methods to add a new data source to your dashboard.
From flat files to SaaS platforms: What are Power BI data sources?
In Power BI, a data source is the entity from which data is extracted for creating reports and visualizations. It can be a file, webpage, database, or another app/service. Data sources are vital elements of Power BI reports. They provide the raw material for creating reports and visualizations.
Power BI supports local as well as cloud data sources. This means you can connect Power BI to data sources on the same computer or server and to those that are stored on other servers (in the cloud). For on-premises data sources, you’ll need the Power BI gateway.
Differences in the available data sources in Power BI: Desktop vs Service
Microsoft Power BI comes in two editions:
- Power BI Desktop
- Power BI Service
There is a greater diversity of data sources for Power BI Desktop. It features a variety of 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, the data sources in Power BI Service 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.
Eager to learn more? Check our article on Power BI dashboard examples.
Microsoft Power BI data sources list
Now, let’s take a closer look at the data sources you can connect to in Power BI.
1. 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.
Explore how you can connect API to Power BI.
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.
2. Power BI: Database data sources
You can connect Power BI to many popular data sources and import data for building reports.
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 of 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.
Learn more about Power BI DirectQuery.
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.
3. 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: Information 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 they are not generally available yet).
To connect, go to Get data > More… > Microsoft Fabric, select the preferred source, and click Connect.
4. Power BI: Power Platform data sources
Power Platform is a group of products 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. This is a legacy connector.
- Common Data Service: Data stored in the Microsoft cloud database. This is also a legacy connector and it was 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.
5. Power BI: Azure data sources
To connect, go to Get data > More… > Azure, select the preferred service, and click Connect.
6. Power BI: Online Services data sources
Power BI offers native connectors for many online services and apps.
To connect, go to Get data > More… > Online Services, select the preferred service, and click Connect.
7. 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 the 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.
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.
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 sources, 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 cloud 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 data connector for Shopify in Power BI.
You can connect other (not natively supported) data sources to Power BI using:
- Connector by Coupler.io: This reporting automation platform allows you to connect marketing and business apps to Power BI and create dynamic reports and dashboards.
- 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
This platform supports over 60 cloud data sources, including CRM apps, accounting software, advertising platforms, and so on.
The main advantage of the Power BI connector by Coupler.io 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.
Select the data source you want to connect from the dropdown list below and press Proceed. You will be prompted to create a Coupler.io account first. This can be done in less than a minute and no credit card is required. Then complete three simple steps
Step 1 – Collect data. Connect your source app account and choose the data to export. This could be data entities, metrics, reports, etc.
Step 2 – Transform data. Preview and transform your data before loading it to Power BI. At this stage, you can sort and filter data, add calculable columns, hide and rearrange columns, and blend data from several sources into a single dataset.
Step 3 – Load data and schedule refresh. In the next step, press the Generate button and copy the link.
Proceed to set up your custom schedule for the updates. Toggle on Automatic data refresh and select the times and the days when you want Coupler.io to automatically send fresh data to Power BI. The update interval can vary from once a month to once every 15 minutes, depending on your needs. Once done, Save and Run the importer.
Then, go to Power BI Desktop, select Get data -> From web, and paste the link you copied earlier.
Paste the importer URL in the URL field and click OK.
Now, Power BI will pull the data from your source app 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.
That’s how easily Coupler.io can connect and automate data sources to Power BI.
The list of data sources you can connect to Power BI with Coupler.io
For more details, please check the Data Sources page.
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 about how you can do this in the example of one of the most popular Power BI alternatives – Looker Studio. However, 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 save 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 the Power BI DirectQuery-supported data sources?
Some popular DirectQuery-supported data sources in Power BI are:
- 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
- ODBC
What are the Microsoft Power BI types of data sources supported?
Here are the main types of data sources:
- Flat Files
- Databases
- Microsoft Fabric
- Power Platform
- Azure
- Online Services
- Template apps
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.
Connect your data source to Power BI with Coupler.io
Get started for free
