Connecting to Your Data

Priya runs sales for a regional distributor. Her numbers live in five places. Last month’s figures sit in an Excel file on her laptop. This month’s are in a shared folder that fills up with a new spreadsheet every week. The customer list lives in a company database that IT manages. Two more reports come from a supplier’s website. Every Monday she copies, pastes, and stitches it all together by hand, and by the time she is done the meeting has already started.

Power BI exists to end that Monday. Before it can build a single chart, though, it has to reach your data where it already lives. That reaching is called connecting, and this chapter is about doing it well.

Why this matters

A report is only as good as the data feeding it. If you connect to the wrong file, or to a copy that is three weeks stale, every chart on top of it inherits the mistake. Getting the connection right is the quiet foundation under everything else you will build.

The good news: Power BI can talk to hundreds of data sources, and the pattern is almost always the same. Pick a source, point Power BI at it, preview what is there, and bring in the parts you want. This chapter walks through it with the sources most professionals meet: spreadsheets, folders, company databases, and the web.

A quick definition before we start. A connector is the built-in adapter Power BI uses to speak a particular source’s language. There is one for Excel, one for SQL Server databases, one for the web, and so on. You do not build connectors. You choose the right one from a list.

The Get data button: your front door

Everything starts on the Home ribbon in Power BI Desktop, the free app you install on a Windows PC. The ribbon is the strip of buttons across the top. Near the left you will find Get data.

Select it and Power BI shows a short Common data sources list of the most popular choices. If the source you want is not on that short list, select More to open the full Get Data window, where every connector is grouped into categories: All, File, Database, Microsoft Fabric, Power Platform, Azure, Online Services, and Other.

Pick a source, select Connect, and Power BI takes over from there. The exact next screen depends on what you are connecting to, but the rhythm never changes: point at the source, preview, load.

When Power BI finishes loading, it stores the data as a semantic model, which is simply Power BI’s name for the collection of tables that sits behind your report. For now, read that term as “the data behind the report.”

Common connectors, in plain terms

Here are the sources you are most likely to reach for, with a plain description of when each one fits.

Excel Workbook. The workhorse. If your data lives in an .xlsx or .xlsm file, this connector reads the tables and sheets inside it. Priya’s “last month’s figures” file is a job for this one.

Text/CSV. A CSV is a plain text file where each row is a line and commas separate the values. Most business systems can export a CSV, so this connector is your universal fallback when nothing fancier is available.

Folder. This one is quietly powerful. Instead of pointing at a single file, you point at a folder, and Power BI reads every file inside it as one combined table. This is the answer to Priya’s weekly-spreadsheet problem. Point Power BI at the folder once, and next week’s file joins the report automatically. The catch is that the files must share the same structure, meaning the same columns in the same order.

SQL Server database (and other databases). A database is a system built to store large amounts of structured data reliably, usually managed by an IT team rather than saved as a file. SQL Server is Microsoft’s, and there are connectors for many others too, including Oracle, MySQL, and PostgreSQL. Connecting to one usually means typing a server name and a database name, then signing in. This is how Priya reaches the customer list IT maintains.

Web. The Web connector pulls data straight from a web page or an online file. You paste a URL, and Power BI looks for tables on that page. This covers Priya’s supplier reports. In the From Web dialog, choose Basic and enter one URL to start.

SharePoint folder. If your team stores shared files on SharePoint, Microsoft’s document-library platform, this connector reads a whole SharePoint folder the same way the plain Folder connector reads a folder on your PC. You paste the site URL and sign in with your work account.

That is six connectors. The full list runs to hundreds, but these cover most everyday business data.

The Navigator: preview before you commit

After Power BI connects to a source that holds more than one table, it opens the Navigator window. This is your preview room, and it saves you from loading the wrong thing.

On the left is a list of everything Power BI found: the tables, sheets, or ranges available in that source. Tick the checkbox beside a table and its contents appear as a preview on the right, so you can confirm you have the correct data before bringing anything in. Tick as many tables as you need.

One small thing trips up almost every beginner. The Load and Transform Data buttons stay greyed out until you tick at least one checkbox. If those buttons look dead, you have not selected a table yet.

At the bottom of the Navigator you face one choice with two buttons:

Load brings the data into Power BI exactly as it is. Choose this when the data is already clean and ready.
Transform Data opens Power Query Editor first, where you can clean, rename, and reshape the data before it lands. Choose this when the data needs work, which is most of the time in the real world.

You will meet Power Query Editor properly in the next chapter, where the whole job is cleaning data before it reaches your report. For now, know that Transform Data is the door to it.

Try It You will connect to a simple sales workbook and preview it. If you have a spreadsheet of your own with a clean table of data, use that. Otherwise create one: make an Excel file named Regional Sales.xlsx with a sheet that has the columns Region, Product, Units, and Revenue, and a dozen rows of made-up numbers.

Open Power BI Desktop.

On the Home ribbon, select Get data, then choose Excel Workbook and select Connect.

Browse to Regional Sales.xlsx and select Open.

In the Navigator window, tick the checkbox next to your sales table. Watch the preview appear on the right.

Confirm the columns look right, then select Load.

Your table now appears in the Data pane on the right side of Power BI. What did you notice in the preview? Were the numbers read as numbers, or did anything look like text that should have been a value? Hold that thought. It is exactly the kind of thing the next chapter fixes.

Import or DirectQuery: a copy or a live line

When you connect to some sources, especially databases, Power BI asks a question worth understanding: do you want to Import the data or use DirectQuery? These are called connectivity modes, and the difference matters.

Import makes a copy. Power BI pulls the data out of the source and stores its own compressed copy inside the report file. Every chart then reads from that local copy.

DirectQuery keeps a live line. Power BI stores no data, only a map of the tables and columns. Each time you open a chart or click a filter, Power BI sends a fresh question back to the source and shows what comes back.

A picture helps.

IMPORT
  Source  --- copy at load and each refresh --->  [ Power BI holds the data ]  --->  charts (fast)

DIRECTQUERY
  Source  <--- live question every time --->  [ Power BI holds only the structure ]  --->  charts (always current)

Each mode buys you something and costs you something.

Import is fast, because the data sits right inside Power BI and answers instantly. It is also the more flexible mode and the default choice for new reports. The cost is freshness. A copy is only as current as the last time you refreshed it. Refresh means telling Power BI to pull the data again, which you can do by hand or on a schedule.

DirectQuery is always current, because it reads live from the source every time. The cost is speed and some limits. Charts can feel slower, since each one waits for the source to answer. A single DirectQuery request that would return more than one million rows also fails outright.

One more practical point. You can switch a report from DirectQuery to Import later, but you cannot switch a plain Import report to DirectQuery. So when the choice is genuinely close, leaning toward Import keeps your options open.

How to decide, in plain terms:

Choose Import for most reporting. Monthly sales, quarterly summaries, and anything where a scheduled refresh keeps the data fresh enough. This is the right default.
Choose DirectQuery when the data is too large to copy comfortably, or when people genuinely need to see this minute’s numbers, such as a live operations board.

When in doubt, Import.

Connections, credentials, and keeping it tidy

The first time you reach a source that needs a sign-in, Power BI asks for credentials, meaning the username and password or work account that proves you are allowed to see the data. It then remembers that answer so you do not have to type it again every time.

If a saved sign-in ever goes wrong, for example after a password change, you can manage it. Go to File > Options and settings > Data source settings, pick the source, and edit or clear its stored credentials. That path is the fix for most “it will not connect anymore” moments.

Good habits from day one save real pain later. A few worth adopting:

One source of truth. For any given number, decide which file or database is the official one, and connect only to that. Priya’s whole problem is that “the sales number” lives in five places. Pick one.
Consistent structure. If you use the Folder connector to combine weekly files, every file must have the same columns in the same order. Agree on that layout once and hold everyone to it.
Stable locations. Connect to files in shared, permanent spots like a SharePoint folder, not files that live only on your laptop or move around. A connection breaks the moment the file it points to is renamed or relocated.
Least data needed. Bring in the tables and columns you will actually use. A leaner model is faster and easier to trust.

Data-source hygiene is not glamorous. It is the difference between a report you rebuild every month in a panic and one that refreshes on its own while you sleep.

In business

Return to Priya. Here is how she rebuilds her Monday.

The company database holds the customer list, so she connects with the SQL Server database connector and imports the customer table. The weekly spreadsheets go into one shared folder, and she points the Folder connector at it, so every past and future week loads as a single table. Last month’s stray Excel file moves into that same folder to match. The supplier’s figures come in through the Web connector.

She chooses Import for all of it, because none of it needs to be current to the second, and sets a scheduled refresh to run early each Monday. She writes down which source is official for each number, so no one pastes from the old files again.

Now the stitching that used to eat her Monday happens on its own. She opens the report and the numbers are already there, current as of the overnight refresh. The meeting starts on time.

Common pitfalls

Loading before previewing. The Navigator shows you exactly what is inside a source. Skipping the preview and loading blind is how the wrong sheet ends up in your report. Tick the box, read the preview, then load.
Connecting to a file on your desktop. A file that lives only on your laptop cannot refresh reliably and breaks the moment it moves. Put shared data in a shared, permanent location and connect there.
Reaching for DirectQuery by reflex. “Always current” sounds better than “a copy,” so beginners over-choose DirectQuery and then wonder why their report feels slow. Import is the right default. Use DirectQuery only when size or true live data demands it.
Mismatched files in a Folder connection. Combine files with the Folder connector and one file with an extra column or a renamed header can break the whole combined table. Lock down the layout before you rely on it.
Forgetting the source is separate. Power BI connects to your data; it does not own it. If someone renames the source file or moves the folder, the connection can fail until you point it at the new location.

Practice

Connect to two different sources using two different connectors. Try an Excel file and a CSV, or a file and a public web page with a table on it. Preview each in the Navigator before loading.
Point the Folder connector at a folder that holds two or more files with the same columns. Watch Power BI combine them into one table. Then think about which of your real work files could be organized this way.
Write one sentence for a report you actually need: which single source is the official one for its main number, and why. That sentence is your first act of data-source hygiene.
Open File > Options and settings > Data source settings and look at the list of sources Power BI has remembered for you. Knowing where that screen lives will save you the day a sign-in breaks.

In the next chapter, you take the data you just connected to and clean it in Power Query Editor, the Transform Data door you have already seen. Connecting gets the data in the room. Cleaning gets it ready to work.

Part of the Power BI track. Developed with AI assistance and reviewed by a human editor. Power BI changes often; menu names, features, and prices may have shifted since the “checked on” dates in this chapter. Verify anything critical against Microsoft’s official documentation.