Topic 5: Excel, CSV and Text Files
Excel and CSV files are among the most common data sources you'll encounter when working with Power BI.
Before moving to databases and more advanced sources, it's important to understand how Power BI handles these simple file-based sources.
๐น Excel Files
An Excel workbook can contain multiple:
โข Worksheets
โข Excel tables
โข Named ranges
โข Supporting sheets
โข Lookup tables
When you connect an Excel workbook to Power BI, Power BI shows the available objects through the Navigator.
For example:
Sales_2026.xlsx
โ Sales
โ Customers
โ Products
โ Targets
You can select the data you actually need.
Excel Table vs Worksheet
This is an important distinction. Suppose your Excel sheet contains:
A1: Order ID | B1: Product | C1: Region | D1: Amount
You can use the worksheet directly. However, converting the dataset into an Excel Table is generally a better practice when the data is maintained regularly.
For example: SalesTable
Order ID | Product | Region | Amount
1001 | Laptop | West | 80000
1002 | Monitor | South | 25000
A structured Excel Table makes the dataset easier to manage and can make expanding data more predictable.
๐น Importing an Excel File
When you select: Home โ Get Data โ Excel
Power BI opens the Navigator. You can then:
Load โ Load the selected data into the model.
Transform Data โ Open the data in Power Query first.
For most real-world work, Transform Data is important because raw source data often needs cleaning before it should enter the model.
For example, you may discover:
Amount
80000
25000
N/A
75000
The N/A value needs to be handled before you build calculations using the Amount column.
๐น CSV Files
CSV stands for Comma-Separated Values. A CSV file stores tabular data as plain text.
Example:
OrderID,Product,Region,Amount
1001,Laptop,West,80000
1002,Monitor,South,25000
1003,Laptop,North,75000
Each row generally represents a record, while separators divide the columns.
CSV files are popular because they are:
โข Simple
โข Lightweight
โข Easy to generate
โข Supported by many applications
โข Easy to exchange between systems
They're commonly used for data exports from applications and databases.
๐น Delimiters
Although CSV usually means comma-separated, files can use different delimiters.
For example:
1001,Laptop,West,80000 uses commas.Another file might use:
1001;Laptop;West;80000 using semicolons.Power BI needs to correctly identify the delimiter so that the columns are separated properly. If the wrong delimiter is selected, the entire row may appear as one column.
๐น Text Files
Power BI can also connect to text files where data is stored in a structured format.
For example:
1001|Laptop|West|80000
1002|Monitor|South|25000
1003|Laptop|North|75000