Everything You Need to Know About Choosing the Right Power BI Data Source in Microsoft Power BI Desktop

When you first launch Power BI Desktop, the first thing that comes to mind is, how do I load data? Think about it, when you launch Power BI Desktop, without any data, its an empty shell. The value comes when you load the data and of course build something.

So, in this blog, we will focus on the various data sources in Power BI and also highlight some important factors. For example, we will explore the different types of data sources and storage modes.

Let's Start With The Basics - What are Power BI data sources?

When we say data sources we can also interpret these as the data connectors. So, data connectors are the data sources that Power BI supports and we can ingest or pull data from. For example, if you have an Excel spreadsheet or a SQL Database that contains all your data, these are your data source. For these data sources, Power BI has a relevant data connector. Below, you will see the Get Data window, notice for an Excel spreadsheet we have "Excel Workbook" and for a SQL Database we have "SQL Server database" connectors. So, we can establish a data connection to our data sources through the relevant data connector.

‍

‍

So, hopefully we are aligned and can see that a data source and a data connector are more or less the same thing. For your data source, we should have a data connector to establish a connection to the data source and ingest the data. Further below, we will explore the types of Power BI data sources that exist.

I heard something about Storage Modes?! Are they the same as data connectors?

So, I find a lot of confusion when words such as Import, DirectQuery, LiveConnection, Composite Models and DirectLake are thrown in the mix. For this reason, I will spend some time here as I feel its an important distinction to be aware of. So, do not worry! Once you have finished reading this, it will all make sense.

Storage modes and data connectors in Microsoft Power BI are two different things. As we said, data connectors are simply the data sources that we can ingest or pull data from. However, storage modes are the methods available for establishing a connection and interacting with the underlying data source. Check out the helpful diagram below to see the distinction.

‍

‍

A single data connector can offer multiple storage modes. For example, when connecting to "Azure Analysis Services database", we can choose between Import or a LiveConnection storage mode.

To put this into context, when we first launch Power BI Desktop, we are presented with a blank canvas - no data, measures, visuals and nothing pretty! To start, we must select "Get Data", which displays all the available DATA CONNECTORS. After choosing a data connector from the list, as shown in the diagram below, we can select from the available STORAGE MODES. However, if the data source only supports one storage mode, such as Excel, you won’t see options for choosing a storage mode.

‍

‍

To make sure the above is clear, my data source is Azure Analysis Services, Power BI has the data connector "Azure Analysis Services database" and we have two storage modes, Import and LiveConnection.

If I select Import, the data will be physically loaded and stored into the Power BI Desktop file, therefore the underlying PBIX. This will increase it's memory and size. If I select LiveConnection or "Connect Live", the data will not be physically stored in Power BI Desktop, therefore all interaction with a report using a LiveConnection will directly query the existing Analysis Services model.

I will no longer explore Storage Modes below, but focus on data sources and data connectors, so here is a summary:

• Data Source: Where the data lives (e.g. Excel, SQL, Salesforce)
• Data Connector: The option you choose in Power BI to connect to a data source.
• Storage Mode: How Power BI established a connection (Import, DirectQuery, LiveConnection, etc.) to the data source.

If you want a deep dive into the different Storage Modes which you can see below through our detailed diagram, be sure to read: Power BI Storage Modes Demystified.

‍

Difference between Microsoft Power BI Service and Power BI Desktop?

Before we dive into the details of data sources in Power BI, it’s important to understand the difference between Power BI Desktop and the Power BI Service, because it affects how and where you connect to your data.

In short:

• Power BI Desktop is the free application installed locally on your machine, used to build and develop reports.
• Power BI Service, also known as Power BI Cloud, is the online platform used to share, collaborate and view reports. You can access it through app.powerbi.com.

While Microsoft has recently added more development features to the Power BI Service, Power BI Desktop is still the recommended tool for building your reports and models. So, when establishing a connection to your data sources, transforming your data and designing your model.

Yes, you can build reports in the Service. For example, clicking the button “+ New report” online will let you start from sources like Excel (Preview), CSV (Preview), Manually Entered Data or a published semantic model. However, these options are limited, as you can see below:

‍

‍

With the above said, the Power BI Service does have a powerful tool called Power BI Dataflows. To understand this, it's easy to think of it as Power Query (Data Transformation) but made available online. It allows users to prepare and transform data in the cloud. It’s great for self-service data modelling, especially in larger teams. Even though Dataflows are a completely separate blog and out of scope, check out the DATA SOURCES they have:

‍

‍

Exploring Types of Power BI Data Sources - Get Data Window

As of now, I believe we have over 150 available data sources in Power BI with their relevant connector, and if you follow the monthly Power BI updates, its fairly common to see more being added. So the list is continuously growing/changing.  

To categorise the different data sources in the best way, we will follow the six categories that appear in the Get data window, as you can see below. There is a long data sources list.

‍

File-Based Data Sources in Power BI

The File category in Power BI includes some of the most common data sources for analysts and business users. As the name suggests, these are data sources stored in a file format, usually local or cloud-based files that you open, upload or share. They can support different data type.

These sources are especially popular because they’re easy to work with, don’t require database connections and often come from exported reports, spreadsheets or lightweight tools. You’ll often pass through the Power Query editor before loading the data into your report. To be clear, not promoting these to be your data sources for large enterprise solutions 😉

Here’s a quick look at each file type and what it’s used for:

• Excel Workbook: Connects to .xlsx files and supports tables and data.
• Text/CSV: Connects to simple comma or delimited files, ideal for quick extracts and lightweight exports.
• XML: Structured markup format often used in legacy systems or data exports.
• JSON: Commonly used in APIs and modern web apps, semi-structured and highly flexible.
• Folder: Loads all files from a folder, useful for batch processing or combining files with identical structure.
• PDF: Extracts tabular data from PDF documents.
• Parquet: A columnar file format optimised for big data, often used in data lakes and cloud platforms.
• SharePoint Folder: Connects to files stored in SharePoint Online document libraries.

‍

‍

Database Data Sources in Power BI

The Database category in Power BI includes connections to a wide range of enterprise systems where structured data is stored. Storage is typically in relational or analytical formats.

These data sources are often at the heart of business operations, used to manage everything from customer records and transactions to inventory, finance and performance metrics. Unlike files, databases are designed for scalability, security, and multi-user access, making them a solid choice for reliable and refreshable reporting. Especially in medium to large organisations.

While many of these are traditional relational databases (like SQL Server or Oracle), others are built for analytical workloads (such as Analysis Services or Snowflake), optimised for fast querying over massive datasets.

Given the number of supported databases (as you can see from the long list), we won’t go through every single one. Instead, here’s a quick intro to some commonly used ones:

• SQL Server database: Microsoft’s relational database, one of the most widely used with strong Power BI integration.
• Oracle database: Popular enterprise RDBMS often used in legacy and large-scale systems.
• Snowflake: A modern cloud data platform designed for fast querying and elastic scalability.
• SAP HANA database: In-memory, column-oriented database optimised.
• IBM Netezza: High-performance data warehouse appliance built for advanced analytics.

‍

‍

Microsoft Fabric Data Sources in Power BI

The Microsoft Fabric category represents the new unified data platform from Microsoft, bringing together data engineering, data science, real-time analytics and business intelligence - all under one roof. It goes well beyond data visualization, reporting, semantic models, etc.

This section is where you'll find cloud-native components built specifically for and within the Fabric ecosystem, allowing organisations to build scalable, end-to-end analytics solutions natively in the Microsoft environment.

Here’s a quick explanation of each connector in this category:

• Power BI semantic models: Formerly “datasets”, these are reusable data models published to the Power BI Service.
• Dataflows: Power Query Online-based ETL (Extract-Transform-Load), perfect for reusable self-service data prep.
• Datamarts (preview): A no-code, end-to-end solution combining dataflows and SQL database for business users. Forever in Preview 😉
• Warehouses: SQL-based storage layer optimised for structured, relational data at scale.
• Lakehouses: Combines data lake flexibility with the structure of a warehouse, great for unstructured and structured data
• KQL Databases: Optimised for real-time telemetry and log data using the Kusto Query Language.

‍

‍

Power Platform Data Sources in Power BI

The Microsoft Power Platform category is all about connecting Power BI to Microsoft’s broader low-code ecosystem, particularly Dataverse and Dataflows, which are commonly used by tools like Power Apps and Power Automate.

While Power Apps and Power Automate themselves don’t appear as direct connectors, Power BI can connect to the underlying data these tools use, like Dataverse. This supports reporting and analysis. These connectors are ideal for organisations that use Power Apps and automated workflows using Microsoft’s business application platform.

You’ll notice a couple of legacy options here too, as Microsoft transitions towards newer services.

Here’s a breakdown of what you see:

• Power BI dataflows (Legacy): Older version of dataflows (Gen1) used before Fabric integration, still functional for existing setups.
• Common Data Service (Legacy): Previous name for Dataverse, used to store data from apps built in Power Apps and Dynamics 365.
• Dataverse: The core data platform for the Power Platform, a scalable and secure data service used by apps in Microsoft 365.
• Dataflows: As explained in the Fabric category section, Power Query Online-based ETL (Extract-Transform-Load), perfect for reusable self-service data prep.

‍

‍

Azure Data Sources in Power BI

The Azure category allows Power BI to connect directly to Microsoft’s powerful cloud platform - Azure. This is where you’ll find cloud-based databases, storage services and big data tools designed for high performance, scale, security and streamlined data integration.

You’ll also notice some familiar names from the Database category we explored above, such as SQL Server and Analysis Services. That’s because these are cloud-hosted versions of tools traditionally run on-premises.

There are many options here, so we won’t cover each one in detail, but here are some of the key connectors you’ll likely come across:

• Azure SQL Database: A cloud-native relational database service, highly scalable and integrates seamlessly with Power BI.
• Azure Blob Storage: Used to store unstructured data like files and logs.
• Azure Data Lake Storage Gen2: A highly scalable storage layer for big data analytics, built for hierarchical file management.
• Azure Databricks: A collaborative platform for data science and engineering, often used with Spark-based pipelines.
• Azure Cosmos DB: A globally distributed NoSQL database with high availability and low latency.

‍

‍

Online Services Data Sources in Power BI

The Online Services category in Power BI includes cloud-based applications and platforms that many organisations use day-to-day. Think SharePoint, Dynamics 365, Salesforce and Google Analytics.

These connectors allow Power BI to plug directly into business-critical systems and marketing tools to pull live or regularly refreshed data for reporting and analysis. This very much depends on the data source, the connector and any limitations.

You’ll also see some overlap here with earlier categories. For example, we mentioned that Dynamics 365 stores data in Dataverse, which you’ll also find under the Power Platform category. But here, it's surfaced as part of a broader set of cloud services.

Here are a few of the most common connectors:

• SharePoint Online List: Connects directly to lists in SharePoint Online, great for tracking tasks, forms and workflows
• Dynamics 365 (Dataverse): Microsoft’s unified data platform used by Dynamics 365 apps, perfect for CRM and business operations data.
• Salesforce Objects: Connects to the raw data entities (tables) stored in your Salesforce environment.
• Salesforce Reports: Connects to pre-built reports in Salesforce, faster to set up but less flexible than the objects connector.
• Google Analytics: Pulls web and marketing performance data from your GA account into Power BI for traffic, goal and audience analysis.

‍

‍

Other Data Sources in Power BI

The Other category is a bit of a mixed bag. It includes specialised connectors, third-party integrations and technical interfaces for more advanced or niche data scenarios.

A few of these connectors are in beta as you can see from the screenshot or tailored for specific tools and platforms, but you’ll also find some powerful, widely-used options here that are worth calling out:

• Web: Allows you to pull data from public or authenticated websites using a URL, useful for scraping tables, APIs or HTML data.
• ODBC: A flexible connection option that lets Power BI tap into any system with an ODBC-compliant driver, great for custom or legacy systems.
• SharePoint List: Not to be confused with “SharePoint Online List” as this one connects to on-prem SharePoint lists.
• Active Directory: Enables queries on your organisation’s directory (users, groups, devices), useful for IT and HR reporting. I use this connector for Row Level Security (RLS). Also, this is part of what’s now branded as Microsoft Entra.

‍

‍

We have now gone through the range of data sources and their relevant connectors within the six categories. Of course, when you have identified the relevant connector, you simply click Connect, and follow the on-screen instructions to ingest or connect to the data.

What are Certified Connectors?

As you can see in the Get Data window below, there is a "Certified Connectors" button in the bottom left. Certified connectors are custom-built data connectors developed by third parties and approved by Microsoft. While Microsoft distributes them as part of Power BI, the maintenance and support responsibility lies with the original developer.

The key benefit? These connectors are safe to use without modifying security settings, and they appear directly in the Get Data menu alongside Microsoft’s native connectors.

‍

‍

What are Template Apps?

Also, alongside "Certified Connectors" you can see "Template Apps". These are pre-built Power BI solutions you can install from the Microsoft AppSource. They’re designed to save time by connecting to your data and populating a ready-made report. No design from scratch required.

Think of them as plug-and-play analytics kits for tools like Microsoft 365, Salesforce or GitHub, perfect for getting started with reporting fast.

‍

‍

Preview, Beta & Legacy Connectors – What Do They Mean?

As we explored the various data sources and connectors above, you may have noticed: Preview, Beta or Legacy. I mentioned near the start of this blog that we have monthly updates and with this Power BI evolves. Below is a description for each:

• Preview: These data connectors are early versions made available for testing and feedback. They might have limited functionality and are not recommended for production environments. ​
• Beta: Similar to Preview, Beta connectors are in the testing phase and may not support all features. They are intended for evaluation purposes and should be used cautiously in production settings.​
• Legacy: These are older versions of connectors that have been superseded by newer alternatives. While still available, it's advisable to use the updated versions to benefit from improved features and support.

Conclusion

So, we've seen throughout this blog the various data of data sources that exist. I also want to mention, whether you're working with flat file types like Excel and CSV or enterprise systems such as SQL Server or Snowflake, the data connection category (data connectors) you choose can also impact performance, flexibility and governance. We saw that Power BI Desktop connects to a variety of data sources and each comes with its own strengths and Power BI lets you edit the query in Power Query Editor before loading it into your model. Finally, always be mindful of sources marked as Preview, Beta or Legacy.