Imagine you've just been handed a reporting project. The sales data lives in an Excel file on a shared drive. The product catalog is a CSV export from an old system. Customer transaction history sits in a SQL Server database. And your competitor's pricing? Someone's been manually copying that from a website into a spreadsheet every week — a task you're about to automate.
Before you can build a single chart or write a single DAX formula, you need to bring all of that data into Power BI Desktop. This is the foundational step that everything else depends on, and it's where a lot of beginners get stuck — not because it's technically difficult, but because each data source has its own quirks, authentication requirements, and gotchas that nobody warned them about.
By the end of this lesson, you'll know how to connect Power BI Desktop to four of the most common data sources in the real world: Excel workbooks, CSV files, SQL Server databases, and live web pages. You'll understand not just the how, but the why behind each connection type, so you can troubleshoot problems when they arise and make smart decisions about how to structure your data pipeline.
What you'll learn:
You should have Power BI Desktop installed on your machine (it's free — download it from the Microsoft Store or from powerbi.microsoft.com). You don't need any prior Power BI experience, but you should be comfortable opening files on your computer and have a basic understanding of what a spreadsheet looks like. For the SQL section, it helps to know that a database contains tables, but you don't need to know SQL syntax.
Before we dive into specific connectors, it's worth understanding what's actually happening when you connect to a data source in Power BI. Think of Power BI Desktop as having two distinct engines under the hood: a query engine (called Power Query) and an analytical engine (called the Vertipaq engine, which stores the data model).
When you connect to a data source, you're not just importing raw data. You're creating a query — a set of instructions that tells Power BI how to find, retrieve, and transform your data. That query can run once (importing a snapshot of your data) or it can run on demand (pulling fresh data every time you refresh). This distinction matters enormously when you're deciding how to connect to different sources.
Every connection in Power BI starts in the same place: the Get Data dialog. To open it, look at the ribbon at the top of Power BI Desktop. On the Home tab, you'll see a button labeled "Get data" — either the main button itself or a dropdown arrow next to it. Click the dropdown arrow to see a short list of common sources, or click "More..." to open the full dialog with over 100 connectors. For this lesson, we'll be using four of those connectors.
Key concept: Power BI stores your connection queries in a component called Power Query Editor. You can think of this as the staging area where data gets cleaned and shaped before it enters your model. We'll keep things simple here and focus on establishing connections, but know that every connector we use opens a door into Power Query.
Excel is probably the most common data source you'll encounter as a Power BI user. Nearly every organization has critical data living in .xlsx files, and Power BI handles them beautifully — as long as the data is structured reasonably well.
On the Home tab in the ribbon, click the dropdown arrow on "Get data," then select "Excel workbook." A file browser window will open. Navigate to your Excel file and click "Open."
Let's say we're working with a file called regional_sales_2024.xlsx. After Power BI opens it, you'll see the Navigator pane — a two-panel dialog that shows everything Power BI found inside the workbook on the left, and a preview of whichever item you select on the right.
This is where many beginners get confused. Power BI distinguishes between three things it can find inside an Excel file:
You can select any of these, but named tables are almost always the right choice. When you import a full worksheet, Power BI pulls in every row and column that Excel considers "used," which often includes empty rows, header rows that aren't really headers, and leftover formatting from cells you deleted three months ago. A named table, by contrast, has explicit boundaries and a clear header row.
Best practice: Before connecting Power BI to an Excel file you control, take 30 seconds to format your data range as a table in Excel. Select the data, press Ctrl+T, confirm the headers, and give it a descriptive name in the Table Design tab. Your future self will thank you.
In the Navigator, select the checkbox next to your table (for example, SalesData). The preview on the right will show you the first few rows. If the data looks right, click Load to import it directly, or Transform Data to open Power Query Editor first. For now, click Load.
Power BI will import the data and you'll see a new table appear in the Fields pane on the right side of the screen.
CSV (Comma-Separated Values) files are the universal language of data exports. Almost every system — ERP platforms, e-commerce tools, accounting software, legacy databases — can spit out a CSV. They're simple text files where each row is a record and each column is separated by a delimiter, usually a comma.
Go to "Get data" → "Text/CSV." Browse to your file and click Open.
Unlike Excel, there's no Navigator step. Power BI jumps straight to a preview dialog that shows you what it thinks the file looks like. Let's say we're loading product_catalog_export.csv.
At the top of the preview dialog, you'll see three dropdown menus that are easy to overlook:
é instead of é, or squares where there should be accented letters), change this setting. Windows-1252 is the culprit for most Western European encoding issues.Below those settings, you'll see a preview of your data with column headers derived from the first row of the file (Power BI assumes the first row contains headers, which is correct 95% of the time).
If everything looks right, click Load. If you need to adjust column types or remove unwanted columns, click Transform Data to open Power Query Editor.
Warning: CSV files have no concept of data types. Every value is just text until Power BI assigns types. Power BI will try to detect them automatically, but it often gets dates wrong — especially if your dates are in formats like
DD/MM/YYYY, which Power BI can misread asMM/DD/YYYY. Always double-check your date columns after loading a CSV.
If Excel and CSV are the campfires of data, SQL Server is the power plant. It's where large-scale, transactional data lives — orders, inventory, customer records, financial transactions. Connecting Power BI to SQL Server unlocks far more data than you could realistically manage in flat files.
Before you click anything, you need to make one important decision: Import mode or DirectQuery mode.
For most beginners, Import mode is the right starting point. Use DirectQuery when data freshness requirements make imports impractical (think real-time dashboards or tables with millions of rows that change constantly).
Go to "Get data" → "SQL Server." A dialog box will appear with two fields:
SQLSERVER01, 192.168.1.50, or mycompany.database.windows.net.Leave the mode as "Import" and click OK.
This is where many beginners hit their first wall. A dialog will ask how you want to authenticate:
Choose the appropriate option, enter credentials if needed, and click Connect.
You'll land in the Navigator, which shows a tree of databases, and within each database, a list of tables and views. This is where the SQL Server connection gets exciting — you'll often see dozens or hundreds of tables.
Let's say we're looking at a database called SalesDB. Expand it and you'll see tables like dbo.Orders, dbo.Customers, dbo.Products. Select one or more tables by checking their boxes. The preview pane shows you a sample of rows from each selected table.
Pro tip: In SQL Server, you'll often see both tables and views. A view is essentially a saved query that looks like a table — it might join several underlying tables or filter data in specific ways. Views are often cleaner to import than raw tables, especially if a database architect has already done the work of preparing the right data structure for reporting.
When you've selected your tables, click Load (or Transform Data if you want to shape the data first). Power BI imports the data and you'll see your tables appear in the Fields pane.
This one surprises a lot of people. Power BI can connect to a publicly accessible web page and extract structured data — specifically, HTML tables — directly from the page. No scraping tools, no APIs, no code required. It refreshes automatically when you publish to the Power BI service.
This is genuinely useful for things like exchange rates, government statistics, sports standings, and any publicly published table you'd otherwise copy manually.
Go to "Get data" → "Web." A dialog box appears with a single field: URL. Enter the full URL of the web page containing the data you want, then click OK.
For example, let's say you want to pull in a table of ISO country codes from a Wikipedia page — the kind of reference data that never changes much but is annoying to maintain manually. You'd enter the full Wikipedia URL and click OK.
Note: Power BI's Web connector works best with pages that contain actual HTML
<table>elements. It won't reliably extract data that's dynamically loaded via JavaScript (common on modern single-page applications). If you try to connect to a heavily JavaScript-driven page, you may get an empty Navigator or incomplete data.
After a few seconds (Power BI is fetching and parsing the page), the Navigator will appear showing a list of everything it found: Table 0, Table 1, Table 2, and so on. Web pages often have many tables — navigation menus, layout tables, and data tables all look the same to Power BI's parser.
Click through each one in the list and watch the preview. You're looking for the one that contains the actual data you want. Once you find it, select it and click Load.
The resulting table will update every time you refresh your Power BI dataset, pulling the latest version of that web page's content.
Warning: Web connectors are sensitive to page structure changes. If the website redesigns their layout and moves the table to a different position on the page, or renames columns, your connection may break. For critical data, consider a more stable source like an API or database.
Let's put all four connection types together in a single practice session. You'll need to create some sample files for the first two.
Step 1: Create a sample Excel file.
Open Excel and create a table with columns: OrderID, CustomerName, Region, SalesAmount, OrderDate. Add 10 rows of realistic-looking data. Format it as a table (Ctrl+T) and name the table Orders. Save the file as practice_orders.xlsx.
Step 2: Create a sample CSV file. Open Notepad and paste the following:
ProductID,ProductName,Category,UnitPrice
P001,Wireless Keyboard,Electronics,49.99
P002,Ergonomic Mouse,Electronics,35.00
P003,Standing Desk Mat,Office Supplies,29.99
P004,USB-C Hub,Electronics,59.99
P005,Notebook Set,Office Supplies,12.50
Save it as products.csv.
Step 3: Connect to Excel.
Open Power BI Desktop. Click Get Data → Excel workbook. Browse to practice_orders.xlsx. In the Navigator, select the Orders table and click Load.
Step 4: Connect to CSV.
Click Get Data → Text/CSV. Browse to products.csv. Verify the delimiter is comma and the preview looks correct. Click Load.
Step 5: Connect to a Web page. Click Get Data → Web. Enter this URL (a stable Wikipedia table that works well for practice):
https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes
In the Navigator, click through the tables until you find one with country names and codes. Select it and click Load.
Step 6: Verify your work. In the Fields pane on the right side of Power BI Desktop, you should now see three tables listed. Click on each one to confirm the data loaded correctly. If any columns have the wrong data type (indicated by the icon to the left of the column name in Power Query), double-click the table name in the Fields pane, which opens Power Query Editor where you can correct types.
Mistake 1: Importing an entire Excel sheet instead of a named table.
The symptom is extra blank rows, columns named Column1, Column2, or a header row that Power BI treats as data. The fix is to go back to Excel, format the data as a named table, then re-connect.
Mistake 2: Date columns loading as text in CSV files.
This happens constantly. Look for a calendar icon next to a column name in the Fields pane — if you see ABC (text) instead of a calendar, Power BI misread the type. In Power Query Editor, click the column header, then click the data type icon at the top left of the column and change it to Date.
Mistake 3: SQL Server connection failing with "Login failed."
First, confirm you're using the right authentication method — Windows vs. Database. Second, confirm the server name is correct (pay attention to named instances, which look like SERVERNAME\INSTANCENAME). Third, ask your DBA if your account has been granted access to the specific database, not just the server.
Mistake 4: Web connector returns empty tables or wrong data.
Try clicking through all the tables in the Navigator — the one you want might be Table 3 instead of Table 0. If all tables are empty, the page likely loads its data dynamically via JavaScript, which the basic Web connector can't handle.
Mistake 5: Connecting to the same data source twice by accident. It's easy to click "Get Data" twice and create duplicate queries for the same file. Check the Queries pane in Power Query Editor (View → Queries pane) to see if you have duplicate entries. Delete the redundant one by right-clicking it and selecting Delete.
Mistake 6: File path breaking when moving files. If you connect to an Excel or CSV file and then move it, Power BI will error on refresh because the path no longer exists. To fix this, go to Home → Transform data → Data source settings, find the file connection, and update the path.
You've just covered the four connection types that will handle the majority of real-world Power BI projects. Let's recap what each one is best suited for:
| Source | Best For | Watch Out For |
|---|---|---|
| Excel | Ad hoc data, manually maintained files | Sheet vs. table imports; extra blank rows |
| CSV | System exports, legacy data, bulk transfers | Encoding, delimiters, date formats |
| SQL Server | Large transactional datasets, enterprise data | Authentication, Import vs. DirectQuery |
| Web | Public reference tables, scraped data | JavaScript-rendered pages, layout changes |
The critical mental model to walk away with is this: every connection you create in Power BI is not a one-time action but a repeatable query. When you hit Refresh, Power BI runs all your connection queries again, pulling fresh data from each source. This is what makes Power BI reports "live" — not the visuals themselves, but the connections underneath them.
Where to go from here:
Connecting to data is the front door of Power BI. Now that you can open it reliably, you're ready to start building something worth seeing.