Webbo3 Data Analysis Bootcamp · Power BI Module · Lesson 1

Connecting to Data in Power BI: Excel, CSV, MySQL, and the Get Data Menu

A foundational lesson covering how to bring data into Power BI from Excel files, CSV files, and MySQL databases, plus a complete tour of the Get Data menu and how to keep your reports current with data refresh.

Power BI is not a spreadsheet. It is not a database. It is a visualization and analysis engine that sits on top of your data, wherever that data lives. Before you can build a single chart or write a single DAX measure, you must connect Power BI to a data source. That connection is the pipeline through which raw data flows into your report. If the pipeline is poorly built, everything downstream, your transformations, your relationships, your visualizations, will be compromised. This lesson teaches you to build that pipeline correctly. You will learn to connect to Excel files, CSV files, and MySQL databases. You will understand the full Get Data menu so you know what options are available even if you do not use them all today. And you will learn how to refresh data connections so your reports always reflect the latest information, not yesterday's snapshot.

1. The Get Data Menu Overview

The Get Data menu is the front door of Power BI. It is where every data journey begins. Understanding its structure saves you from hunting through submenus when you already know what you need.

Opening the menu. In Power BI Desktop, click the Home tab on the ribbon, then click the Get Data dropdown. You will see three sections: Common Data Sources, More, and Recent Sources. The Common Data Sources section shows Excel workbook, Text/CSV, SQL Server, Web, and a few others you will use frequently. The More option opens a full dialog with dozens of connectors organized by category: File, Database, Azure, Online Services, and Other. Recent Sources shows connections you have used before, which is convenient when you are iterating on a report and reconnecting to the same files repeatedly.

Understanding connector types. Power BI connectors fall into two categories: import mode and DirectQuery. Import mode copies data from the source into Power BI's internal data model, stored in a compressed columnar format called VertiPaq. This gives you the fastest query performance and the full power of DAX and Power Query transformations. DirectQuery leaves the data in the source database and sends live SQL queries every time a visual refreshes. This is necessary for very large datasets that cannot fit in memory, or for real-time dashboards where data changes minute by minute. For most bootcamp projects and small-to-medium business reports, Import mode is the correct choice. DirectQuery adds complexity and slower performance that you should only accept when the data volume genuinely demands it.

Authentication and privacy settings. When you connect to a data source for the first time, Power BI prompts you for credentials. The options are Windows authentication, which uses your logged-in Windows account; Database authentication, which requires a username and password; and Anonymous, for public web sources or files with no protection. After entering credentials, Power BI may ask about privacy levels: Public, Organizational, or Private. This setting controls whether Power BI combines data from different sources in the query engine or keeps them isolated for security. For bootcamp projects where all data is local and non-sensitive, you can set everything to Public to avoid unnecessary query folding restrictions. In production environments with sensitive data, set the appropriate level and consult your organization's data governance policy.

The Navigator window. After selecting a data source and providing credentials, Power BI opens the Navigator window. This shows a preview of available tables, sheets, or objects from the source. You can select one or more items to load. A crucial checkbox at the bottom says Transform Data. Always check this box. It opens the Power Query Editor instead of loading raw data directly into your model. The Power Query Editor is where you clean, reshape, and validate data before it enters your report. Skipping this step and loading data directly is a habit that produces broken reports later, because dirty data reaches your visualizations unchecked.

2. Connecting to an Excel File

Excel is the most common data source for Power BI in business environments. Most organizations store their departmental data in Excel workbooks before they graduate to a formal database. Power BI connects to Excel natively and can read individual worksheets, named tables, and even specific cell ranges.

Step-by-step connection. Click Home → Get Data → Excel workbook. Navigate to your file and select it. The Navigator window opens, showing every worksheet and every named table in the workbook. A named table in Excel is a range that has been converted to an Excel Table using Ctrl + T and given a name in the Table Design tab. Named tables are strongly preferred over raw worksheet ranges because they automatically expand when new rows are added, and Power BI recognizes them as distinct objects with their own schema.

Selecting the right object. If your Excel file has a sheet called SalesData with a named table inside it called tblSales, the Navigator will show both SalesData and tblSales. Always select the named table, tblSales, not the sheet. The sheet object includes every cell on the sheet, including empty cells, headers, and stray notes that will corrupt your data model. The named table is a clean, bounded data object with proper column headers and data types. If your Excel file does not have named tables, go back to Excel, select your data range, press Ctrl + T, and give it a meaningful name before connecting to Power BI.

Handling multiple sheets. If your workbook contains multiple related tables, for example a Sales sheet, a Products sheet, and a Customers sheet, you can select all three in the Navigator by holding Ctrl while clicking each one. Then click Transform Data to open all three in the Power Query Editor simultaneously. This is the correct workflow for building a relational model: each table becomes a separate query in Power Query, and you define relationships between them in the Model view after loading.

Data type detection and correction. When Power BI imports Excel data, it guesses data types based on the first 200 rows. If a column contains mostly numbers but has text in row 250, Power BI may import it as a number and fail on the text rows, or import it as text and lose numeric functionality. In the Power Query Editor, inspect every column's data type icon, the small icon in the column header. Click it to change the type if the guess is wrong. For currency values, choose Fixed Decimal Number. For dates, choose Date, not Date/Time, unless you genuinely need time components. For IDs and codes that look like numbers but should not be summed, choose Text to prevent Power BI from treating them as metrics.

File path considerations. Power BI stores the absolute file path to your Excel workbook in the connection settings. If you move the file to a different folder, the connection breaks. For team projects, store the Excel file in a shared network location or OneDrive folder and use that path. Better yet, migrate to SharePoint or a database once the report matures. For personal bootcamp projects, keep the Excel file in the same folder as your Power BI file and use a relative path structure to minimize breakage when you zip and submit your project.

3. Connecting to a CSV File

CSV files are the lingua franca of data exchange. Every system on earth can export CSV. It is a plain text format where each row is a record and commas separate the columns. Because CSV has no formatting, no formulas, and no multiple sheets, it is simpler than Excel but also easier to corrupt if the data contains commas or line breaks inside text fields.

Step-by-step connection. Click Home → Get Data → Text/CSV. Navigate to your file and select it. Unlike Excel, Power BI does not show a Navigator with multiple objects. It shows a preview of the file contents with a sample of the first few rows. At the bottom, you choose the delimiter: Comma, Semicolon, Tab, or Custom. Comma is the default and correct for standard CSV files. If your file uses semicolons because it was generated in a European locale where commas are decimal separators, select Semicolon instead.

Encoding issues. CSV files do not store encoding information. If your file contains non-English characters, accented letters, or names with special characters, Power BI may display them as garbled symbols if it guesses the wrong encoding. In the preview window, check the File Origin dropdown at the top. If your data looks wrong, try switching from 65001: Unicode (UTF-8) to 1252: Western European (Windows) or vice versa. For Nigerian data containing Yoruba, Igbo, or Hausa names with tonal marks, UTF-8 is usually correct, but some legacy systems export in Windows-1252. Verify the preview before loading.

Handling headers and data types. The preview window shows a checkbox for Column headers are in the first row. Ensure this is checked if your CSV has a header row, which it almost always does. Power BI will use those headers as column names. If unchecked, Power BI generates generic names like Column1, Column2, and you must rename everything manually. As with Excel, inspect data types in the Power Query Editor after loading. CSV files have no native data type information, so Power BI guesses everything from the text content. Dates are especially prone to misinterpretation. A column with dates in DD/MM/YYYY format may be parsed as text if the first rows look ambiguous. Use the locale settings in Power Query to force the correct date format: right-click the column, choose Change Type → Using Locale, select Date, and pick the locale that matches your data format, for example English (United Kingdom) for DD/MM/YYYY.

Quoted fields and embedded commas. A well-formed CSV wraps text fields in double quotes when they contain commas or line breaks. For example: "Lagos, Nigeria", 50000, "2026-03-15". Power BI's CSV parser handles standard quoted fields correctly. However, if your CSV was generated by a custom script or an older system that does not quote properly, you may see data shifted into the wrong columns. In the Power Query Editor, use the Split Column or Replace Values transformations to fix malformed rows. If the corruption is widespread, go back to the source and request a properly quoted CSV export.

CSV versus Excel for Power BI. For bootcamp projects, use whichever format your data is provided in. For production workflows, prefer named Excel tables or databases over CSV. CSV has no schema, no data validation, and no incremental refresh capability. Every refresh reloads the entire file. An Excel table or a database connection can be configured for incremental refresh, loading only new rows since the last update, which is critical for large datasets.

4. Connecting to a MySQL Database

Connecting Power BI to a MySQL database is where you transition from file-based analysis to enterprise-grade reporting. Instead of refreshing a static file, your report queries a live database that may be updated by multiple applications throughout the day. This connection requires the MySQL Connector/NET, a driver that Power BI uses to communicate with MySQL servers.

Installing the MySQL connector. Before you can connect, you must install the MySQL Connector/NET from the MySQL official website. Go to dev.mysql.com/downloads/connector/net and download the latest version for your operating system. Run the installer with default settings. Restart Power BI Desktop after installation. Without this connector, MySQL will not appear as an option in the Database section of the Get Data menu. If you attempt to connect and receive an error stating the connector is missing, verify the installation and restart Power BI.

Step-by-step connection. Click Home → Get Data → More → Database → MySQL database. In the dialog that appears, enter your server name. If MySQL is running on your local machine, this is localhost. If it is on a remote server, enter the IP address or hostname provided by your database administrator. Enter the database name, for example webbo3_retail. Leave the optional advanced options blank for now. Click OK.

Authentication. Power BI prompts for credentials. Choose Database and enter your MySQL username and password. The default root username is root, and the password is the one you set during MySQL installation in the SQL module. Check the box to encrypt the connection if your server supports SSL. For local bootcamp projects, this is optional. For production databases, always use encrypted connections. Click Connect. If the connection fails, verify that the MySQL server is running, that the credentials are correct, and that the server accepts connections from your machine's IP address. Remote servers often have firewall rules that block unknown IPs.

The Navigator window for databases. After connecting, the Navigator shows every table and view in the database. Unlike Excel, where you might select multiple sheets, with databases you should select only the tables you need. Importing every table in a large database bloats your Power BI model and slows refresh times. Select your core fact table, for example orders or sales, and the related dimension tables, for example customers, products, and stores. Hold Ctrl to select multiple tables. Check Transform Data and click OK to open the Power Query Editor.

SQL statement versus table selection. In the Navigator, you can click Advanced options before connecting to enter a custom SQL statement instead of selecting whole tables. This is powerful when you need only a subset of columns or rows, or when you want to pre-join tables at the database level before importing into Power BI. For example, you might write: SELECT o.order_id, c.first_name, c.last_name, o.total_amount, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date >= '2026-01-01'. This reduces the data volume imported into Power BI and leverages the database's indexing for faster filtering. However, writing SQL here bypasses Power Query's query folding optimization for some transformations, so use it only when the performance gain is clear. For bootcamp projects, selecting tables and transforming in Power Query is usually the better learning path.

DirectQuery versus Import for MySQL. When connecting to MySQL, Power BI asks whether to use Import or DirectQuery. Import copies the data into Power BI's VertiPaq engine. DirectQuery sends live SQL queries to MySQL every time a visual updates. Choose Import for bootcamp projects and for most business reports where the dataset is under a few million rows. Choose DirectQuery only if the dataset is too large to fit in memory, if you need real-time data, or if your organization's security policy prohibits data export from the database. DirectQuery has significant limitations: some DAX functions are unavailable, performance depends on your database server's speed, and you cannot use the Power Query Editor to merge or append queries from other sources.

5. Refreshing Data Connections

A report that shows yesterday's data is a historical artifact, not a decision-making tool. Refreshing is the process of updating your Power BI model with the latest data from the source. Understanding refresh options is essential for maintaining report accuracy and performance.

Manual refresh in Power BI Desktop. While developing your report, click Home → Refresh or press Ctrl + Shift + R. This reloads all data sources connected in Import mode. For DirectQuery sources, this button has no effect because the data is not stored locally. For Import mode, the refresh time depends on data volume, network speed, and the complexity of your Power Query transformations. A refresh of a few thousand rows takes seconds. A refresh of millions of rows with complex joins and calculations may take minutes or longer. Monitor the status bar at the bottom of Power BI for progress.

Refresh in Power BI Service. When you publish your report to Power BI Service, the cloud platform, you must configure a refresh schedule for Import mode datasets. Go to the dataset settings in Power BI Service, expand the Scheduled refresh section, and define how often to refresh: daily, multiple times per day, or weekly. You can set up to eight refreshes per day for Pro licenses, more for Premium. For file-based sources like Excel or CSV stored in OneDrive or SharePoint, Power BI Service can refresh automatically when the source file changes. For database sources, Power BI Service needs a Gateway installed on a machine inside your network that has access to the database. The Gateway acts as a secure bridge between the cloud and your on-premises data.

Incremental refresh. For large datasets, full refresh is wasteful. If your sales table has ten years of history and only yesterday's rows are new, reloading ten years of data every time is inefficient. Incremental refresh allows you to define a date range and load only new or changed rows. In Power BI Desktop, right-click your table in the Data view, choose Incremental refresh and real-time data, and configure the parameters: the date column to partition by, the number of historical years to keep, and the number of recent days to refresh. This is an advanced feature typically used in production, but understanding it now prepares you for real-world datasets that grow over time.

Credential management. When you publish a report, Power BI Service does not carry your local credentials with it. You must re-enter credentials in the dataset settings for each data source. For Excel and CSV files in OneDrive, authenticate with your Microsoft account. For MySQL databases, re-enter the database username and password. If a Gateway is required, configure the Gateway connection and map the data source name to the Gateway's configured connection. Failure to configure credentials after publishing is the most common reason reports show stale data or fail to refresh entirely.

Handling refresh failures. When a scheduled refresh fails, Power BI Service sends an email notification to the dataset owner. The error message usually indicates whether the failure is due to a missing file, a database connection timeout, invalid credentials, or a transformation error in Power Query. For file-based sources, the most common cause is that the file was moved, renamed, or deleted. For database sources, network issues, server downtime, or credential expiration are typical culprits. Always test your refresh manually in Power BI Desktop before publishing, and verify the first scheduled refresh in Power BI Service to catch configuration issues early.

Best practice for bootcamp projects. Keep your source files in a single folder alongside your Power BI file. Use relative paths or at least stable paths. Before submitting your project, perform a full refresh, verify that all visuals update correctly, and save the file. If your instructor opens your report and sees broken visuals or stale data because the file path points to your local C: drive, your project will be marked down regardless of how good the design is.

Using AI to Move Faster in Power BI Data Connections

The mechanics of connecting to data sources are straightforward once you know the steps. AI can help you troubleshoot errors, choose the right connector, and optimize your refresh strategy before you encounter problems.

1. Diagnose connection errors with natural language.
If Power BI throws an error like "The credentials provided are invalid" or "The specified module could not be found" when connecting to MySQL, paste the exact error message into Copilot or ChatGPT and ask: "What does this Power BI MySQL connection error mean, and what are the step-by-step fixes?" AI will explain that the MySQL Connector/NET is missing, that the server name is wrong, or that the database user lacks remote access privileges. This saves you from searching through forums for an answer that may be outdated or irrelevant to your specific setup.

2. Use AI to choose between Import and DirectQuery.
If you are unsure whether your dataset size warrants DirectQuery, describe your situation to AI: "I have a MySQL database with 500,000 rows of sales data and five related dimension tables. Should I use Import or DirectQuery in Power BI?" AI will explain that 500,000 rows compress efficiently in VertiPaq and Import mode is likely faster and more feature-rich. It will also note that if the data changes every minute and users need real-time dashboards, DirectQuery might be justified despite the performance trade-offs. Use this guidance to make an informed decision, then test both modes if you are still uncertain.

3. Generate SQL for Advanced Options.
When connecting to MySQL, the Advanced Options dialog lets you enter a custom SQL statement to pre-filter data before import. If you know you need only active customers from Lagos who registered after January 2026, but you are unsure how to write the JOIN and WHERE clause, ask AI: "Write a MySQL query that selects order_id, customer_name, total_amount, and order_date from the orders and customers tables, joining on customer_id, filtering for active customers in Lagos with orders after 2026-01-01." Paste the generated SQL into the Advanced Options dialog. This reduces the data volume imported into Power BI and speeds up your model.

4. Optimize refresh schedules with AI guidance.
For production reports, ask AI: "I have a Power BI report connected to an Excel file on OneDrive and a MySQL database. How should I configure refresh in Power BI Service to ensure the Excel data updates when the file changes and the MySQL data refreshes every four hours?" AI will explain that OneDrive files support automatic refresh on change, while MySQL requires a Gateway and scheduled refresh. It will walk you through installing the Gateway, configuring the data source in Power BI Service, and setting the refresh schedule. This prevents the common mistake of configuring scheduled refresh for a OneDrive file, which is unnecessary and wastes your refresh quota.

5. Verify AI-generated SQL and connection settings before production use.
AI can write SQL and suggest connection configurations, but it does not know your database schema, your network topology, or your security policies. Always test AI-generated SQL in MySQL Workbench first to confirm it returns the expected columns and rows. Always verify that the server name, database name, and credentials in your Power BI connection match your actual environment. A query that works in AI's example database may fail in yours because a column is named customer_name instead of cust_name. The same verification rule applies here as everywhere: AI accelerates, but you own the correctness.

A habit worth building from this lesson onward: every time you connect to a new data source, document the connection details in a text note inside your Power BI file or a separate readme document. Record the file path or server name, the authentication method, the tables imported, and the refresh schedule. When a connection breaks six months later, this documentation saves hours of detective work. AI can help you draft this documentation from your description, but the discipline of keeping it updated is yours.

Next lesson: data transformation and cleaning in the Power Query Editor.