4 Connecting to Data Sources: Excel, CSV, databases

Why Data Connections Matter

Every Power BI report begins with a connection to data. Before you can build a single chart or dashboard, Power BI needs to know where your data lives. This chapter walks you through connecting to the three most common data source types Excel workbooks, CSV files, and relational databases and explains the general workflow that applies to all of them.

4.1 The General Connection Workflow

How Power BI Connects to Data

Regardless of the source type, every connection in Power BI follows the same sequence. You tell Power BI where to find the data, preview it, shape it in Power Query Editor, and then load it into the data model ready for building visuals.

flowchart TB
    A[Get Data] --> B[Choose Source]
    B --> C[Connect & Authenticate]
    C --> D[Navigator / Preview]
    D --> E[Transform Data<br/>Power Query Editor]
    E --> F[Close & Apply]
    F --> G[Data Model Ready<br/>for Visuals]
    classDef default fill:#2e4057,color:#ffffff,stroke:#ff9933,stroke-width:3px,rx:10px,ry:10px;

Always Use “Transform Data” — Not “Load”

When the Navigator or preview dialog appears, resist clicking Load directly. Instead, always click Transform Data to open Power Query Editor. This gives you the opportunity to inspect your data, fix data types, remove unwanted columns, and clean any issues before they enter your model. Once you are satisfied, click Close & Apply to load the data.

4.2 Connecting to Excel

About Excel as a Data Source

Microsoft Excel is the most common data source in Power BI projects. Whether it is a finance team’s monthly report or an operations team’s tracking sheet, Excel files are everywhere. Power BI connects to both .xlsx and .xls formats and can read individual sheets or named Tables within a workbook.

4.2.1 Steps to Connect

How to Connect to an Excel File

To connect Power BI to an Excel workbook:

In the Home ribbon, click Get Data → Excel Workbook
Browse to your file and click Open
The Navigator pane will appear, it lists all sheets and named Tables found in the workbook
Select the sheet or Table you want to import
Click Transform Data to open Power Query Editor
Review and clean the data as needed
Click Close & Apply to load it into the data model

Watch: Connecting Power BI to an Excel File

The video above demonstrates the full Excel connection workflow, from Get Data through to Close and Apply.

Navigator Pane — Selecting Your Data

The Navigator pane shows a preview of your selected sheet or Table on the right side. Use this preview to confirm that the data looks correct before proceeding.

Named Tables vs. Sheets

When selecting data in the Navigator, prefer named Excel Tables (formatted with Insert → Table in Excel) over raw sheets. Named Tables have clean, defined headers and automatically expand when new rows are added to the source file, making your Power BI refresh more reliable.

Watch Out for Formatted Report Sheets

Excel sheets used as formatted reports with merged cells, totals rows, or decorative headers will import that clutter into Power BI. Use Power Query Editor to clean up these issues before loading. Whenever possible, keep a dedicated “data” sheet in your workbook that is a plain, clean table.

4.3 Connecting to CSV Files

About CSV as a Data Source

CSV (Comma-Separated Values) files are a universal data exchange format. System exports, database extracts, and data shared between teams frequently arrive as CSV. Power BI handles CSV connections cleanly, though you will need to verify a few settings on import.

4.3.1 Steps to Connect

How to Connect to a CSV File

To connect Power BI to a CSV file:

In the Home ribbon, click Get Data → Text/CSV
Browse to your file and click Open
Power BI shows a preview dialog with auto-detected delimiter and encoding settings
Verify the preview looks correct columns should be properly separated
Click Transform Data to open Power Query Editor
Set data types explicitly for each column (dates, numbers, text)
Click Close & Apply to load the data

CSV Preview Dialog

[😀 of the Power BI CSV preview dialog showing the file origin, delimiter dropdown, and data preview]

The preview dialog detects the delimiter automatically. If columns appear merged or incorrectly split, change the Delimiter dropdown to match your file’s actual separator (comma, semicolon, tab, etc.).

Always Set Data Types After Importing CSV

CSV files carry no type information ,every value arrives as plain text. After opening Power Query Editor, explicitly set the data type for every column using the type icon in the column header. Pay special attention to date columns and numeric ID columns, which are frequently misdetected.

4.4 Connecting to Databases

About Databases as a Data Source

Relational databases are the backbone of enterprise data. Power BI connects natively to SQL Server, Azure SQL, PostgreSQL, MySQL, and many others. Database connections are the most scalable option suitable for large datasets and production reporting environments.

4.4.1 Steps to Connect

How to Connect to a Database

To connect Power BI to a relational database:

In the Home ribbon, click Get Data and select your database type (e.g., SQL Server)
Enter the server name and optionally the database name
Choose your authentication method (Windows, Database username/password, or Microsoft Account for cloud databases)
Click Connect
The Navigator pane lists all available tables and views in the database
Select the tables you need
Click Transform Data to open Power Query Editor
Apply any filters or transformations needed
Click Close & Apply to load the data

Navigator Pane — Selecting Database Tables

[😀 of the Power BI Navigator pane connected to a SQL Server database, showing a list of tables on the left and a preview of a selected table on the right]

You can select multiple tables at once. Power BI will load each as a separate table in your data model, where you can then define relationships between them.

Import vs. DirectQuery

When connecting to a database, Power BI offers two connectivity modes:

Import — Copies data into Power BI for fast performance. Data is refreshed on a schedule.
DirectQuery — Queries the database live every time a report visual loads. Best for real-time data or very large datasets.

For most reporting use cases, Import is the recommended starting point.

On-Premises Databases Require a Gateway

If your database lives on a server within your organization’s network (not in the cloud), you will need to install an on-premises data gateway to enable scheduled refresh when the report is published to the Power BI service. Plan for this early if you are building production reports.

4.5 The Power Query Editor

Your Data Transformation Workspace

After clicking Transform Data from any source connection, you land in Power Query Editor. This is where you shape your data before it enters the model. Every action you take here is recorded as a step in the Applied Steps panel, making your transformations repeatable and auditable.

Power Query Editor Interface

[😀 of the Power Query Editor showing the ribbon, query list on the left, data preview in the center, and Applied Steps panel on the right]

Common tasks in Power Query Editor include removing unnecessary columns, renaming columns, setting data types, filtering rows, and merging or appending queries from multiple sources.

flowchart TD
    A[Source Data\nExcel / CSV / Database] --> B[Power Query Editor]
    B --> C[Remove Unnecessary Columns]
    B --> D[Set Data Types]
    B --> E[Filter Rows]
    B --> F[Rename & Reorder Columns]
    C & D & E & F --> G[Close & Apply]
    G --> H[Clean Data Model]
    classDef default fill:#2e4057,color:#ffffff,stroke:#ff9933,stroke-width:3px,rx:10px,ry:10px;

Close & Apply — Loading Data into the Model

When you are done transforming your data in Power Query Editor, click Close & Apply in the Home ribbon. Power BI will execute all your transformation steps against the source data and load the results into the data model. You will see a loading progress bar, and once complete, your tables appear in the Data pane, ready for building reports and visuals.

4.6 Choosing the Right Source

Which Source Type Should You Use?

The right data source depends on where your data currently lives and the needs of your report. Use the guide below as a starting point.

Source	Best For	Refresh in Service
Excel	Internal team data, analyst-maintained files	OneDrive / SharePoint (no gateway needed)
CSV	System exports, partner data, one-time loads	OneDrive / SharePoint or gateway
Database	Production data, large datasets, enterprise reporting	Gateway (on-premises) or direct (cloud)

Keep Your Source Data Clean

Power BI is a visualization and analysis tool, not a data cleaning tool. The cleaner your source data ( consistent formats, no merged cells, proper column headers, no blank rows), the faster and more reliably your reports will build and refresh. Invest time in source data quality before connecting.

Summary

Concept	Description
Source Categories
Get Data	Single entry point listing all available data connectors
File Connectors	Excel, CSV, JSON, XML, and PDF as common file inputs
Database Connectors	SQL Server, Oracle, MySQL, and other relational engines
Online Service Connectors	SharePoint, Dynamics, Salesforce, and Google Analytics
Web Connector	Scraping HTML tables or calling REST APIs from a URL
Folder Connector	Combining many files in a folder into a single table
Mechanics
Power Query Editor	Editor where source-level transformations are authored
Connection Settings	Credentials, privacy levels, and refresh schedule per source