Lesson 1 of 11 · 9 min · Getting data in

Power BI basics: connecting to data sources

Covers: Power BI Desktop and the Power BI service. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • What Power BI is made of: Desktop, the service and mobile
  • Connect to Excel, CSV, SQL databases and web data
  • Choose between Import, DirectQuery and Live connection
  • Understand credentials, privacy levels and refresh
  • Avoid the common first-day mistakes

In simple words

Power BI is a tool for turning data into interactive reports and dashboards. You build the report on your computer in Power BI Desktop, then publish it to the Power BI online service so others can open it in a browser. Before you can draw a single chart you need to bring data in: connecting is like plugging the tool into a spreadsheet, a database or a web page, and deciding whether to copy the data in or to ask the source each time.

Words to know

  • Power BI Desktop: the free Windows application where you connect to data, model it and design reports.
  • Power BI service: the online site where reports are published, shared and refreshed.
  • Data source: where the data lives, such as an Excel file, a SQL database or a website.
  • Refresh: updating the report's data from its source.

The pieces

  • Power BI Desktop is a Windows application, free to download from Microsoft. (Mac users usually build in the browser-based editor or use a Windows virtual machine.)
  • The Power BI service (app.powerbi.com) hosts published reports and semantic models (the data and model behind a report, earlier called datasets). Sharing, scheduled refresh and dashboards live here.
  • Mobile apps show published reports on phones.
  • The gateway is a small program on your network that lets the online service reach data that is not on the internet, such as a database inside a company.

The typical flow: Get data → shape it (Power Query) → model it (relationships and measures) → build visuals → publish → share and refresh. This course follows that order.

Connect to data

In Power BI Desktop: Home > Get data. The common sources:

Source Notes
Excel workbook choose sheets or Excel Tables; Tables are best because they expand as data grows
Text/CSV check the delimiter, encoding (use UTF-8) and data type detection
Folder combine many files with the same layout, such as monthly exports
SQL Server, PostgreSQL, MySQL and other databases server name, database, and optionally a SQL query
SharePoint, OneDrive refreshes in the service without a gateway
Web a page or an API address
Online services Google Analytics, Salesforce, and many more

After picking a source, the Navigator shows what is available and a preview. Choose Load to bring data straight in, or Transform Data to open Power Query and clean it first (the next lesson). Beginners should nearly always click Transform Data: raw data almost always needs something fixed.

When you connect to a database, prefer connecting to a view or table and let Power BI do the shaping, rather than pasting a complicated SQL query that the next person cannot see inside Power Query. Select only the columns and tables you need; extra columns slow everything down.

Storage modes: Import, DirectQuery, Live connection

How data is stored decides speed, freshness and what you can do:

Mode What it does Good for Watch out
Import copies a compressed snapshot of the data into the model most reports; fastest; full DAX and Power Query data is as old as the last refresh; model size limits apply
DirectQuery sends queries to the source each time someone interacts very large data, or needing near-real-time numbers slower visuals; the source database is under load; some DAX and Power Query features are limited
Live connection connects to an existing published model or Analysis Services model reusing one governed model for many reports you cannot change the model itself
Composite mixes Import and DirectQuery tables in one model big fact table in DirectQuery with small imported dimensions more complexity

Default to Import unless you have a clear reason. It gives the best interactivity. Microsoft Fabric adds a mode called Direct Lake for data stored in Fabric lakehouses; the principles are the same.

Credentials, privacy and security

  • When you connect, Power BI asks how to sign in (Windows, database user, organisational account, or anonymous for public files). Credentials are stored for the data source on your machine and in the service for scheduled refresh.
  • Privacy levels (Public, Organizational, Private) control how data from different sources may be combined, to stop private data being sent to another source accidentally. If you see a "formula firewall" or privacy error, check them.
  • Never put passwords in the report or share a file that contains data you may not share: a Power BI file includes the imported data.
  • For row-level restrictions (a manager sees only their own region), you define row-level security in the model (the sharing lesson).

Refresh

Imported data is a snapshot. To update:

  • In Desktop: Home > Refresh.
  • In the service: set a scheduled refresh on the semantic model (up to a certain number of times a day depending on licence). Cloud sources refresh directly; on-premises sources need a gateway.
  • Refresh needs the credentials to still be valid; an expired password is the most common cause of failure. The service shows refresh history and error messages.

Design for refresh: use Excel Tables or database tables with stable names and columns; a renamed column in the source breaks the report. Keep file paths stable (use SharePoint or a shared folder rather than a path on your laptop).

First-day checklist

  1. Start from a clear question and sketch the report on paper.
  2. Connect only the data you need.
  3. Click Transform Data and look at the types and errors before loading.
  4. Check row counts against the source after loading.
  5. Save early; Power BI files (.pbix) are large, so keep them out of email.
  6. Save a version before big changes, or use a Power BI project (PBIP) format with Git if you work in a team.

Common mistakes

  • Loading data without looking at it, so types and errors cause problems later.
  • Choosing DirectQuery because "live data sounds better", then suffering slow visuals.
  • Connecting a local file path that others cannot reach, breaking scheduled refresh.
  • Importing every column and table "just in case".
  • Sharing a .pbix file that contains private data.

Try it yourself

  1. Install Power BI Desktop (or use a Windows machine) and connect to an Excel file with a Table of sales.
  2. Click Transform Data and look at the preview, column types and any error cells; close without applying changes.
  3. Connect to a CSV file and check that the delimiter and encoding are right.
  4. Write down for your own project whether Import or DirectQuery fits and why.

Check yourself

What is the difference between Power BI Desktop and the Power BI service?

Desktop is the free Windows application for connecting to data, modelling and building reports. The service is the online site for publishing, sharing and scheduled refresh.

Which storage mode should you start with and why?

Import: it is the fastest and supports the full range of Power Query and DAX, at the cost of data being as fresh as the last refresh.

What does a gateway do?

It lets the Power BI service reach data sources that are not on the public internet, such as on-premises databases, for refresh and DirectQuery.

Why choose "Transform Data" instead of "Load" for new data?

To check and fix types, errors and shape in Power Query before the data goes into the model.

Interview questions

Explain Import versus DirectQuery.

Import copies the data into the model and is fast but static until refreshed. DirectQuery queries the source live on every interaction, which keeps data current and avoids size limits but is slower, loads the source and limits some features.

What can cause a scheduled refresh to fail?

Expired or changed credentials, a gateway that is offline, renamed or deleted source columns or tables, timeouts, or the model exceeding size or time limits.

What is a semantic model?

The data, relationships and measures behind one or more reports, published to the service; it was formerly called a dataset.

Summary

  • Build in Power BI Desktop, publish to the service, and use a gateway for on-premises data.
  • Get data from files, databases, the web and services; click Transform Data before loading.
  • Use Import by default; DirectQuery for huge or live data; Live connection to reuse a model.
  • Plan for credentials, privacy levels and refresh from the start.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on Power BI and dashboards for Data Analyst and scores every answer, with what to fix.