Power BI Data Sources: Connecting for Better Insights

Data source in Power BI is the foundation for building dashboards and reports, and that enables users to import, connect, and transform data from various platforms for insightful, interactive visualizations.

This guide shows you what Power BI Data Sources are, and the difference between data sources in Power BI Desktop and Power BI service. You’ll also discover how to create a dashboard in Power BI, the difference between a Power BI dashboard and a report, and how the Power BI report builder works.

You’ll be introduced to Power BI data visualization and how to represent any Power BI data model in the Sankey Diagram in Power BI. There are some Power BI report examples and Power BI design ideas to help you get a good grasp of the Power BI tool.

If you’re unsure about the relationship between Power BI and AWS, there’s a section in this guide that reveals just that. Some sections uncover Power BI Advanced features like the Power BI connectors and the Power BI Data Marts.

What are Power BI Data Sources?

Definition: Power BI Data Sources are the various platforms, services, and file types from which Power BI can connect to retrieve data for reporting and analysis.

What is the Difference Between Data Sources in Power BI Desktop and Power BI Service?

• Data Connectivity
  • Power BI Desktop:

Allows connection to a wide range of data sources like local files (Excel, CSV, JSON), on-premises databases (SQL Server, Oracle), cloud sources (Azure, SharePoint), and APIs.

• Power BI Service:

Used for accessing datasets already published. It connects to cloud-based services (like Microsoft 365, Google Analytics, and Salesforce), and uses Power BI Dataflows.

• Data Preparation and Modeling
  • Power BI Desktop:

Offers full data transformation and modeling capabilities. Users can create calculated columns, relationships, measures, and complex data models using Power Query and DAX.

• Power BI Service:

Focused on consuming and sharing reports. It lacks the full flexibility of the desktop version.

• Gateways for On-Premises Data
  • Power BI Desktop:

Directly connect to on-premises data without needing a gateway.

• Power BI Service:

Requires an On-Premises Data Gateway to refresh or access on-premises data sources.

How many Types of Power BI Data Sources?

• File-Based Sources

Include Excel, JSON, XML, CSV, and SharePoint Folder.

• Database Sources

Connects to on-premises and cloud-based databases like Oracle, SQL Server, MySQL, IBM DB2, PostgreSQL, and Access.

• Online Services

Integrates with services like SharePoint Online, Microsoft 365, Salesforce, Dynamics 365, Google Analytics, and more.

• Azure Sources

Includes Azure SQL Database, Azure Synapse Analytics, Azure Data Lake, and Azure Blob Storage.

• Web Data

Pulls data from websites using HTML tables or Web URLs.

• Big Data and Advanced Sources

Connect to Spark, Hadoop, Google BigQuery, and other advanced analytics platforms.

• Other

Include OData feeds, R/Python scripts, and Power BI datasets (or dataflows).

What is the Power BI Data Sources List?

• File-Based Sources

Connect to local files like Excel, XML, CSV, JSON, and PDF. It comes in handy when managing data in static formats or spreadsheets.

• Database Sources

It includes connections to on-premises and cloud databases like MySQL, SQL Server, PostgreSQL, Oracle, IBM DB2, and Access. Best fit for structured data stored in relational databases.

• Online Services

Enables integration with cloud platforms like Dynamics 365, Google Analytics, Microsoft 365, SharePoint Online, and Salesforce. Suited for pulling data from cloud-based business tools.

• Azure Data Sources

Connect to Microsoft Azure services like Azure Blob Storage, Azure SQL Database, Azure Synapse, and Azure Data Lake—a great fit for organizations using Azure cloud infrastructure.

• Web and API Sources

Extract data from websites using REST APIs or Web URLs via the Web connector. Used for external or third-party data feeds.

• Other Sources

Include OData feeds, dataflows, Power BI datasets, and R/Python scripts for data manipulation and advanced analytics.

What are the Common Connection Methods in Power BI Data Sources?

• Import Mode:

This method supports fast performance and full data modeling capabilities. However, it requires manual or scheduled refreshes to update data.

• DirectQuery:

This method supports real-time data but may have slower performance and modeling limitations. Data remains in the source system and is queried live when users interact with reports.

• Live Connection:

Connect directly to external models like Power BI datasets or SQL Server Analysis Services without importing data. It’s a great fit for enterprise environments where centralized data models are used.

• Push Datasets:

Data is pushed into Power BI through APIs for real-time streaming and dashboard updates.

How to Connect to Power BI Source Data?

• Open Power BI Desktop: Launch the Power BI Desktop — the primary tool for report creation and data modeling.
• Get Data: Click the “Get Data” button on the Home ribbon. A window opens listing available data sources, categorized by type — databases, files, or online services.
• Select Data Source: Choose your desired source (like SQL Server, Excel, or Web) and click Connect.
• Provide Connection Details: Enter the necessary credentials, file paths, server names, or URLs based on the source.
• Preview and Transform Data: Power BI opens Power Query Editor, which allows you to preview, clean, and transform data before loading.
• Load Data: Click load to import data into Power BI’s model or choose Transform Data for advanced shaping.
• Start Building Reports: After loading, the data will be ready for creating reports and visuals.

How to Visualize Source Data in Power BI?

• Log in to Power BI, enter your email, and click the “Submit” button.

• You’ll be redirected to a Microsoft account. Enter your password, and click “Sign.”

• You can opt to stay signed in.

Creating a dataset and selecting the data to use in the required chart.

• The Power BI Datasets below will be used for the Sankey Diagram in the report/dashboard.

• To add data to your report, click on the “Paste data into a blank report.”

• Paste the data table above into a blank table, name it, and click on the “Create a blank report” button.

• Click on “Expand All” to see the chart metrics. Check the dimensions and metrics.

• Click on “Get more visuals.”

• Search “ChartExpo” and select the Sankey Diagram. Add all the charts in your visuals since they’ll be used in the business dashboard for different business insights.

• Click on the “Add” button.

• After you have added the chart to your visual, you’ll see the chart icon in your visuals list. These charts will be used in the report/dashboard one after the other.

• To add the Sankey Diagram visual, click on the chart icon and choose the dimension and measures.

• In the visualization’s properties, click on License Settings and add the key.

• After adding the key, you’ll see the Sankey Diagram.

• At this point, set the properties to customize the look and feel of the chart. First, you’ll have to change the title of the chart.

• You’ll have to set the node colors for each level.

• You’ll have to set a link or level color direction for each level.

• Here’s the final look and feel of the chart.

Insights

Lead and Conversion Tracking Report

• Visualizes customer journey stages: from lead generation to final purchase.
• Highlights drop-offs and conversion rates at each funnel stage.
• Perfect use of a Power BI Sankey diagram to show the flow and volume of users.

FAQs

How do I find the source of data in Power BI?

In Power BI Desktop, navigate to Home > Transform Data > Data Source Settings to view and manage the data sources. It shows connection details and gives room for you to change or clear sources.

How do I create a data source in the Power BI report server?

In Power BI Report Server, you can create a data source by navigating to the Report Server portal and selecting Manage > Data Sources. Add a new connection with the required server and database details.

Can Power BI use Excel as a data source?

Yes, Power BI can use Excel as a data source. There’s the option to import or connect to Excel files (.xlsx, .xls) to load tables, ranges, or Power Query queries for visualization and analysis.

Wrap Up

Power BI Data Sources are the various files, online services, and databases where Power BI imports (or connects) data for analysis.

Power BI Desktop supports broad data transformations and on-premises connections, while Power BI Services focuses on cloud sources and shared datasets. Gateways are required for on-premises data access.

To connect to Power BI data sources, you’ll have to open your Power BI desktop and click the “Get Data” button on the Home ribbon. Choose your preferred source and click Connect. Your preferred source could be Excel, SQL Server, or Web.

You’ll have to provide your connection details. After that, preview and transform the data. Load the data, and finally start building reports.

Now you know what a Power BI data source is, and the role it plays during visualizations. What’s the most preferred data source of your organization?