Text and Folder Data Sources
Learn how to import text files into Power BI Desktop, connect to folders containing multiple files, combine compatible datasets, and prepare file-based data using Power Query.
1. Learning Objectives
Many organizations store exported records as text files or maintain separate files for different days, months, departments, or branches. Power BI can connect to text-based sources and, when the files have compatible structures, combine data from multiple files in a folder.
By the end of this lesson, you will be able to:
- Understand text-based data sources.
- Import a text or CSV file into Power BI Desktop.
- Connect Power BI to a folder containing data files.
- Understand the purpose of the folder preview.
- Combine files with compatible structures.
- Clean and transform file data in Power Query.
- Identify common folder-import errors.
- Prepare a repeatable workflow for recurring files.
2. Understanding Text Data Sources
A text file stores information as readable characters. Depending on its structure, the information may be organized into rows and columns or stored as unstructured text.
Power BI can use supported text-based formats as data sources. Common examples include CSV files and delimited text files. The exact import settings depend on the file structure and the connector being used.
| File Type | Typical Extension | Common Use |
|---|---|---|
| Comma-separated values | .csv | Exported sales, attendance, and transaction records. |
| Plain text | .txt | Delimited records, logs, and application exports. |
| Tab-delimited text | .txt | Records where fields are separated by tab characters. |
3. How a Text File Becomes a Power BI Table
When you import a structured text file, Power BI interprets its records and separates fields into columns. You should verify the result before loading the data into the model.
Structured text data is interpreted and checked before it becomes a model table.
4. Importing a Text File
Step-by-Step Procedure
- Open Power BI Desktop.
- Select the Home ribbon.
- Choose Get data.
- Select the appropriate text or CSV connector.
- Browse to the file you want to import.
- Review the preview and confirm that the fields are separated correctly.
- Check the first row to determine whether it contains column headers.
- Verify that special characters and text values display correctly.
- Select Transform Data if cleaning or conversion is required.
- Apply the changes and verify the resulting table.
Example: Attendance Text File
Suppose an institute exports daily attendance records into a text file. The file contains StudentID, StudentName, AttendanceDate, and Status fields.
StudentID,StudentName,AttendanceDate,Status
101,Samadhan,2026-01-05,Present
102,Pradnya,2026-01-05,Present
103,Samiksha,2026-01-05,Absent
104,Neha,2026-01-05,Present
After importing the file, verify that each field appears in a separate column. Check the date type and confirm that attendance status values are consistent.
5. Understanding Folder Data Sources
The Folder connector is useful when related files are stored together in one folder. Instead of importing every file individually, Power BI can list the files in the folder and help you combine compatible file contents.
For example, a sales department may save a separate CSV file for each month. If every file follows the same column structure, the monthly records can be combined into a single dataset.
Common Folder-Based Scenarios
- Monthly sales exports.
- Daily attendance files.
- Branch-wise inventory records.
- Weekly service or support reports.
- Recurring transaction exports from another system.
6. How Folder Import Works
Conceptual folder workflow for combining files with compatible structures.
7. Connecting Power BI to a Folder
Step-by-Step Procedure
- Place the related source files in a dedicated folder.
- Check that the files have compatible column names and structures.
- Open Power BI Desktop.
- Select Home → Get data.
- Choose the Folder connector from the available data sources.
- Browse to the folder containing your files.
- Confirm the selected folder path.
- Review the file list in the folder preview.
- Filter out files that should not be included.
- Choose Combine or Combine and Transform Data when the option is available and appropriate.
- Review the sample file and the generated transformation steps.
- Verify the combined rows and columns before applying the query.
8. Folder Preview and File Filtering
A folder connection typically returns a list of files and metadata. The list can include file names, extensions, dates, paths, and file content. The exact columns depend on the connector and environment.
Example of deciding which files belong in a folder-based dataset.
Useful Filtering Checks
- Filter by file extension when only one format is expected.
- Exclude temporary files and backup copies.
- Check file names for unexpected versions or duplicate exports.
- Review folder paths if subfolders are present.
- Confirm that each included file contains the expected fields.
9. Combining Multiple Files
Power BI can help combine multiple files when they follow a compatible structure. The combination process generally uses a sample file to define how the content of each file should be interpreted.
Requirements for a Reliable Combination
- Files should contain the same logical fields.
- Column names should be consistent.
- Headers should be positioned consistently.
- Each file should use a compatible format and delimiter.
- Data types should be compatible across files.
- Extra title rows and summary rows should be handled appropriately.
Example: Monthly Sales Files
The sales team stores three files in a folder:
Sales_January.csv,
Sales_February.csv, and
Sales_March.csv.
Each file contains OrderID, OrderDate, Product, Quantity, and Sales. Power BI can combine the records into one table when the files have compatible structures and the combine process is configured correctly.
The resulting table can then be used to analyze sales across all three months rather than maintaining separate report queries for each file.
Append Versus Merge
| Operation | Purpose | Example |
|---|---|---|
| Append | Adds rows from one table to another. | Combine January sales rows with February sales rows. |
| Merge | Joins tables using one or more matching columns. | Add product category information using ProductID. |
10. What Happens When a New File Is Added?
A well-maintained folder-based query can reduce repetitive manual work. When a new compatible file is added to the source folder, the query can include it during refresh, provided the query filters and transformation steps allow it.
A new file can be included on refresh if it meets the query's requirements.
Refresh Checklist
- Keep the source folder available at the configured location.
- Use a consistent file naming convention.
- Keep the new file's column structure compatible.
- Verify that query filters include the new file.
- Refresh the query and inspect the resulting row count.
- Investigate errors before publishing updated reports.
11. Cleaning Text and Folder Data in Power Query
Power Query provides transformation tools that help prepare imported data before it enters the Power BI model.
| Transformation | Purpose | Example |
|---|---|---|
| Promote Headers | Uses the correct first row as column names. | Convert Column1 and Column2 into StudentID and StudentName. |
| Change Type | Assigns an appropriate data type. | Convert AttendanceDate to Date. |
| Trim Text | Removes leading and trailing spaces. | Convert " Laptop " to "Laptop". |
| Remove Rows | Removes unnecessary records when appropriate. | Remove blank rows outside the dataset. |
| Filter Rows | Includes only records meeting defined conditions. | Exclude records that are not part of the reporting period. |
| Remove Columns | Removes fields that are not required. | Remove an unused export comment column. |
12. Common Problems and Solutions
| Problem | Possible Cause | Recommended Action |
|---|---|---|
| The text file appears in one column | Incorrect delimiter. | Check the file and choose the correct delimiter. |
| Characters display incorrectly | Encoding mismatch. | Review the available encoding settings. |
| Folder contains unexpected files | Backups or unrelated documents are stored with the data. | Filter by name, extension, or other relevant metadata. |
| Combine operation fails | Files have different structures or incompatible formats. | Inspect the files and standardize their structures. |
| New file is missing after refresh | Filters exclude the file, or it is outside the source folder. | Check the folder path and query filters. |
| Refresh reports an error | Source file is missing, inaccessible, or changed. | Verify access, file location, and expected columns. |
| Rows are repeated | The same export was included more than once. | Inspect file names and records before removing duplicates. |
13. Hands-On Practice Assignment
Assignment A: Import a Text File
- Create a CSV file named
Attendance.csv. - Add StudentID, StudentName, AttendanceDate, and Status columns.
- Enter at least five sample records.
- Import the file into Power BI Desktop.
- Verify the delimiter and header row.
- Check the date and text data types.
- Open Power Query and correct any problems.
- Apply the changes and inspect the result.
Assignment B: Combine Monthly Sales Files
- Create a folder named
MonthlySales. - Create three CSV files named
Sales_January.csv,Sales_February.csv, andSales_March.csv. - Give all three files the same column names and compatible data types.
- Place the files in the MonthlySales folder.
- Connect Power BI Desktop to that folder.
- Review the file list and exclude unrelated files.
- Use the combine option when appropriate.
- Inspect the sample file and generated transformation steps.
- Verify that the combined table contains records from all intended files.
- Add another compatible monthly file and test the refresh workflow.
Completion Checklist
- ☐ I can import a text file.
- ☐ I can identify delimiters and header rows.
- ☐ I can connect to a folder containing multiple files.
- ☐ I can filter out unrelated files.
- ☐ I understand the requirements for combining files.
- ☐ I can clean data using Power Query.
- ☐ I understand how new files may be included during refresh.
14. Knowledge Check
Question 1
What is a text-based data source?
Answer: A source that stores information as text, such as a CSV or delimited text file.
Question 2
When is the Folder connector useful?
Answer: When related files are stored together and need to be inspected or combined.
Question 3
Why should files have compatible structures before combining them?
Answer: Compatible structures help Power Query interpret the fields consistently across files.
Question 4
What is the difference between append and merge?
Answer: Append adds rows from tables, while merge joins tables using matching columns.
Question 5
Why should unrelated files be filtered out of a folder query?
Answer: They may introduce unwanted records or cause combination errors.
Question 6
Can a new file be included automatically in a folder-based query?
Answer: It can be included during refresh if the file meets the query's filters and structure requirements.
Question 7
What should you do when files have different column names?
Answer: Inspect the files and standardize their structure or adjust the transformation logic before combining them.
Question 8
Why is Power Query useful for folder-based imports?
Answer: It provides repeatable steps for filtering, cleaning, transforming, and combining data.
Lesson Summary
In this lesson, you learned how to import text-based files, connect Power BI to a folder, inspect the available files, and combine compatible datasets.
You also explored delimiters, encoding, headers, Power Query transformations, file filtering, refresh behavior, and common folder-import errors.