🔌 Connecting Power BI to the World

Before you can build beautiful charts, you need data. Power BI is incredibly powerful because it can connect to almost anything.

When you click the "Get Data" button in Power BI Desktop, you are presented with hundreds of connectors. These generally fall into a few major categories:

1. Flat Files & Folders: The most common starting point. You can connect to .csv, .txt, or .xlsx (Excel) files. You can even connect to an entire Windows folder, and Power BI will automatically combine all the files inside it!
2. Databases: Relational databases like SQL Server, Oracle, or IBM, as well as big data platforms like Databricks and Azure Synapse.
3. Power Platform & Online Services: Connect directly to Dynamics 365, Salesforce, Google Analytics, SharePoint, or even OData feeds and web APIs.
4. Other/Legacy Systems: You can connect to Hadoop, Microsoft Exchange, or Active Directory. You can also start with a "Blank Query" if you are writing custom M-code (the language behind Power BI data connections).
5. Creating Sample Data: If you don't have a file, you can click "Enter Data" on the Home ribbon to manually type or paste a small sample table directly into Power BI.

🗄️ Understanding Storage Modes

Once you select your data source (e.g., an Excel file or a SQL Database), Power BI asks you a very critical question: How do you want to connect to this data?

You must choose a Storage Mode. This dictates whether Power BI pulls the data into its own memory, or leaves the data at the source.

1. 📥 Import Mode (The Default)

In this mode, Power BI pulls a copy of the raw data and stores it inside the .pbix file using its highly compressed in-memory engine.

• When to use: Dataset is less than 1GB, you need the fastest possible report performance, and the data doesn't change every second (e.g., monthly sales reports).
• Limitations: Data is a "snapshot" in time. You must schedule data refreshes to get new data.

2. 📡 DirectQuery Mode

In this mode, no data is imported. Power BI just connects to the source. Every time a user clicks a chart, Power BI sends a live query to the database to fetch the answers.

• When to use: The dataset is massive (e.g., billions of rows in a data warehouse), data changes in real-time (e.g., stock market trades), or strict company security policies prevent copying data.
• Limitations: Visuals load slower because they rely on database speed. Some DAX calculations are restricted.

3. 🟢 Live Connection

This is a special mode used only when connecting to a data model that has already been published to the Power BI Service or Azure Analysis Services.

• When to use: Your company has one "Single Source of Truth" dataset built by the IT team, and 50 different analysts need to connect to it to build their own reports without duplicating the data model.

4. 🧩 Composite Models (Dual)

A hybrid approach! You can set some tables to Import (for speed) and some tables to DirectQuery (for massive real-time data) within the exact same Power BI report.

🥊 Quick Comparison Cheat Sheet

🏋️ Practice Drill

Let's do your first data connection!

Task:

// Try answering these:
1. In Power BI Desktop, click **Get Data** (in the Home ribbon) and select **Excel workbook**.
2. Find any simple Excel file on your computer (even a blank one with a few test numbers) and open it.
3. The **Navigator** window will pop up. Check the box next to your Excel Sheet name.
4. Do **not** click Load yet! Notice the button that says **Transform Data**? We will be clicking that in our next lesson.

← 🏢 Introduction to Power BI | Next Topic → 🧹 Power Query and Data Cleaning