Excel and CSV Data Sources in Power BI
Learn how to connect Power BI Desktop to Excel workbooks and CSV files, select the correct data, preview tables, transform data with Power Query, and prepare reliable data for reporting.
1. Learning Objectives
Data is the foundation of every Power BI report. Before creating charts, measures, or dashboards, you must connect to a suitable source and understand the structure and quality of its data.
By the end of this lesson, you will be able to:
- Explain the difference between Excel and CSV files.
- Connect Power BI Desktop to an Excel workbook.
- Import data from a CSV file.
- Understand the Navigator window and data preview.
- Choose between Load and Transform Data.
- Recognize headers, rows, columns, and data types.
- Identify common import problems and their solutions.
- Prepare a simple dataset for Power BI reporting.
2. Understanding Excel and CSV Data Sources
Power BI Desktop can connect to many data sources, including Excel workbooks and comma-separated values files. Both formats are commonly used to store business data such as sales, inventory, attendance, expenses, and customer information.
What Is an Excel Workbook?
An Excel workbook is a spreadsheet file that can contain one or more worksheets, tables, formulas, charts, and other workbook elements. Power BI can connect to supported workbook content, such as worksheets and Excel tables.
What Is a CSV File?
CSV stands for Comma-Separated Values. It is a text-based format that stores records in rows, with fields separated by a delimiter, commonly a comma. A CSV file does not retain Excel formulas, cell colors, charts, or worksheet formatting.
| Feature | Excel Workbook | CSV File |
|---|---|---|
| Common extensions | .xlsx, .xls | .csv |
| Worksheets | Can contain multiple worksheets. | Usually represents one flat table per file. |
| Formulas | Can contain formulas and calculated cells. | Stores text values, not live Excel formulas. |
| Formatting | Can retain spreadsheet formatting. | Does not retain cell formatting or charts. |
| Typical use | Structured workbooks and multiple tables. | Simple data exchange between applications. |
| Import considerations | Select the correct worksheet or table. | Check delimiter, encoding, headers, and data types. |
3. The Structure of a Good Dataset
Before importing data, inspect its structure. A well-organized dataset usually has one field per column, one record per row, and a single descriptive header for each column.
Example of a clean sales table suitable for importing into Power BI.
Dataset Preparation Checklist
- Use meaningful column names such as OrderID, OrderDate, and SalesAmount.
- Keep each column focused on one type of information.
- Avoid placing totals or notes inside the middle of the data table.
- Check for duplicate records when uniqueness is required.
- Identify blank cells and missing values.
- Verify that dates and numeric values are stored consistently.
- Remove unnecessary title rows above the actual column headers.
4. Connecting Power BI to an Excel Workbook
Use the Excel workbook connector when your data is stored in an Excel file. The connector lets you browse supported workbook content and select the worksheet or table you want to use.
Step-by-Step Procedure
- Open Power BI Desktop.
- Go to the Home ribbon.
- Select Get data.
- Choose Excel workbook. If it is not immediately visible, open the available data-source list.
- Browse to the location of your Excel file.
- Select the workbook and choose Open.
- Wait for the Navigator window to display the available workbook items.
- Select the required worksheet or Excel table.
- Review the preview to verify that the correct data is selected.
- Choose Load to import directly, or Transform Data to inspect and prepare the data first.
Typical workflow for connecting an Excel workbook to Power BI Desktop.
5. Understanding the Navigator Window
After selecting an Excel workbook, Power BI usually opens the Navigator window. It lists the available items that Power BI can access in the selected workbook.
Important Parts of Navigator
- Item list: Shows available worksheets and supported tables.
- Selection indicator: Helps identify the items selected for import.
- Data preview: Shows sample data from the selected item.
- Load: Imports the selected content into the Power BI model.
- Transform Data: Opens Power Query Editor so you can inspect and modify the data.
How to Select the Correct Item
- Read the worksheet or table names carefully.
- Select the item containing the required records.
- Inspect the column names and sample values.
- Confirm that the preview does not contain unrelated notes or blank sections.
- Choose Load or Transform Data based on the condition of the data.
6. Importing a CSV File into Power BI
CSV files are useful when data is exported from another system or application. Since CSV files are plain text, Power BI must interpret the delimiter, text encoding, column names, and values.
Step-by-Step Procedure
- Open Power BI Desktop.
- Select Home → Get data.
- Choose the Text/CSV connector.
- Browse to the required CSV file.
- Select the file and open it.
- Review the detected delimiter and the previewed columns.
- Check whether the first row contains the intended column headers.
- Verify that values appear in the correct columns.
- Select Load or Transform Data.
- After loading, inspect the table in Data view or Model view as appropriate.
Power BI interprets CSV text and separates fields into columns.
Example CSV Content
The following sample shows how a simple sales dataset can be represented in CSV format.
OrderID,OrderDate,Product,Quantity,Sales
1001,2026-01-05,Laptop,2,120000
1002,2026-01-06,Monitor,3,45000
1003,2026-01-07,Keyboard,5,7500
1004,2026-01-08,Mouse,8,6400
In this example, the first row contains column headers. Each following row represents one sales record.
7. Delimiters, Encoding, and Headers
A CSV file may not always use a comma as its delimiter. Depending on the application or regional settings used to create the file, values may be separated by semicolons, tabs, or other supported delimiters.
| Setting | Purpose | What to Verify |
|---|---|---|
| Delimiter | Separates individual fields in a row. | Each field should appear in the correct column. |
| Encoding | Determines how text characters are interpreted. | Names and special characters should display correctly. |
| Header row | Provides column names for the dataset. | The first data record should not be mistaken for a header. |
| Data type | Determines whether values are text, dates, numbers, and so on. | Values should behave correctly in calculations and visuals. |
Example: Wrong Delimiter
Suppose a file contains the following line:
OrderID;Product;Sales
1001;Laptop;120000
1002;Monitor;45000
If the file is interpreted using a comma delimiter, the entire line may appear in a single column. Selecting the appropriate delimiter allows Power BI to separate the fields correctly.
8. Load vs. Transform Data
After selecting a source, Power BI commonly offers two important choices. Understanding when to use each option helps you build a cleaner and more reliable data model.
Choose a direct import or open Power Query for data preparation.
Choose Load When:
- The source data is already well organized.
- Column names are correct and meaningful.
- Data types and values have been checked.
- No significant cleaning or transformation is required.
Choose Transform Data When:
- Column names need correction.
- Blank rows or unwanted columns need to be removed.
- Data types need to be adjusted.
- Text values need trimming or cleaning.
- Several source tables need preparation before loading.
9. Understanding Data Types After Import
A data type tells Power BI how to interpret a column's values. Correct data types are essential for calculations, filtering, relationships, date analysis, and visualization.
| Column | Example Value | Likely Data Type | Reason |
|---|---|---|---|
| OrderID | 1001 | Text or Whole Number | Choose based on how the identifier is used. IDs are not usually added together. |
| OrderDate | 2026-01-05 | Date | Supports date-based filtering and analysis. |
| Quantity | 5 | Whole Number | Represents a count of items. |
| Sales | 120000.50 | Decimal Number or Fixed Decimal Number | Represents a numeric sales amount. |
| Product | Laptop | Text | Represents a category or product name. |
Example: A Number Stored as Text
If the Sales column is imported as text, Power BI may not treat it as a numeric field for arithmetic or aggregation. Review the column type in Power Query and convert it only after confirming that its values can be interpreted correctly.
A value containing currency symbols, unexpected spaces, or regional decimal separators may need cleaning before it can be converted successfully.
10. Practical Example: Importing Sales Data
Imagine that CIIT TRANING INSTITUTE is preparing a Power BI exercise using a sample sales dataset. The dataset contains order IDs, dates, product names, quantities, and sales amounts.
Sample Data
| OrderID | OrderDate | Product | Quantity | Sales |
|---|---|---|---|---|
| 1001 | 2026-01-05 | Laptop | 2 | 120000 |
| 1002 | 2026-01-06 | Monitor | 3 | 45000 |
| 1003 | 2026-01-07 | Keyboard | 5 | 7500 |
| 1004 | 2026-01-08 | Mouse | 8 | 6400 |
Practice Procedure
- Create an Excel workbook with the sample data above.
- Save the workbook as
SalesData.xlsx. - Format the dataset as an Excel table if appropriate.
- Open Power BI Desktop and select Get data → Excel workbook.
- Select the workbook and choose the sales table or worksheet.
- Review the preview and choose Transform Data.
- Confirm that the column names are correct.
- Set OrderDate to Date, Quantity to Whole Number, and Sales to a suitable numeric type.
- Check for blank rows and conversion errors.
- Select Close & Apply to apply the Power Query changes.
- Verify the imported data in Power BI Desktop.
Expected Result
- The sales table appears in the Power BI model.
- Each field has an appropriate data type.
- Product names are readable and consistent.
- Sales values can be used for numeric analysis.
- Order dates can be used in date-based reports.
11. Common Import Problems and Solutions
| Problem | Possible Cause | Recommended Solution |
|---|---|---|
| All CSV values appear in one column | The delimiter was detected incorrectly. | Check the file and select the correct delimiter. |
| Column names appear as data | The header row was not interpreted correctly. | Promote the correct row to headers in Power Query when needed. |
| Some dates are incorrect or invalid | The source date format or locale was interpreted incorrectly. | Verify the source format and use an appropriate locale during conversion. |
| Numeric conversion errors | Values contain text, symbols, spaces, or unexpected separators. | Inspect the values, clean them, and then convert the column. |
| Special characters look incorrect | The file encoding may not match the expected text encoding. | Check the encoding option available in the import process. |
| Unexpected blank rows | The source includes empty records or formatting outside the data. | Inspect the source and remove unnecessary rows in Power Query. |
| Duplicate records appear | The source contains repeated rows or repeated identifiers. | Check whether duplicates are valid and remove them only when appropriate. |
| Excel content is missing from Navigator | The content may not be in a supported or populated worksheet or table. | Check the workbook, save changes, and reconnect to the correct file. |
12. File Locations and Source Maintenance
Power BI needs access to the source file when it must read the data again. If an Excel or CSV file is moved, renamed, deleted, or placed in an unavailable location, refresh may fail.
Good Source Management Practices
- Store source files in a known and stable folder.
- Use meaningful file names and consistent folder structures.
- Avoid moving or renaming files without updating the source path.
- Keep a backup of important source data.
- Document where the data comes from and who maintains it.
- Test refresh after changing the source location.
- For shared reports, plan how the source will be accessed after publishing.
13. Hands-On Practice Assignment
Assignment A: Excel Import
- Create an Excel workbook named
StudentPerformance.xlsx. - Create columns for StudentID, StudentName, Course, TestScore, and TestDate.
- Enter at least five sample student records.
- Save the workbook.
- Import it into Power BI Desktop using the Excel workbook connector.
- Review the Navigator preview.
- Open Power Query and verify the column names.
- Set TestScore to a suitable numeric type and TestDate to Date.
- Apply the changes and verify the imported table.
Assignment B: CSV Import
- Create a CSV file named
StudentPerformance.csvwith the same fields. - Save the file using a consistent delimiter.
- Import the file using the Text/CSV connector.
- Verify the delimiter and header row.
- Check that the student names appear correctly.
- Review the detected data types.
- Correct any issues in Power Query.
- Load the cleaned dataset into Power BI.
- Compare the resulting table with the Excel import.
Completion Checklist
- ☐ I can import an Excel workbook.
- ☐ I can import a CSV file.
- ☐ I can select the correct worksheet or table.
- ☐ I can verify CSV delimiters and headers.
- ☐ I can identify and correct data-type problems.
- ☐ I can use Transform Data before loading when necessary.
- ☐ I understand why source file locations matter for refresh.
14. Knowledge Check
Question 1
What does CSV stand for?
Answer: Comma-Separated Values.
Question 2
Which connector is commonly used to import an Excel workbook?
Answer: The Excel workbook connector.
Question 3
What is the purpose of the Navigator window?
Answer: It allows users to select supported workbook items and preview their data before importing.
Question 4
When should you choose Transform Data instead of loading directly?
Answer: When you need to inspect, clean, or modify the source data before loading it into the model.
Question 5
Why might a CSV file display all values in one column?
Answer: The delimiter may have been detected or selected incorrectly.
Question 6
Why is the correct data type important?
Answer: It allows Power BI to interpret values appropriately for calculations, filtering, and reporting.
Question 7
Does a CSV file retain Excel charts and cell formatting?
Answer: No. CSV stores plain text data rather than workbook formatting and charts.
Question 8
What should you check if imported dates are incorrect?
Answer: Check the source date format, regional interpretation, and data type conversion.
Question 9
Should every duplicate row always be removed?
Answer: No. First determine whether the repeated records are valid for the business scenario.
Question 10
Why should you keep the source file in a stable location?
Answer: Power BI needs access to the source when refreshing data.
Summary
In this lesson, you learned how to connect Power BI Desktop to Excel workbooks and CSV files. You explored the Navigator window, data previews, delimiters, headers, encoding, and the difference between Load and Transform Data.
You also learned how to inspect data types, identify common import errors, prepare a clean dataset, and maintain stable source file locations for future refreshes.