Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

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.
Best Practice: Inspect the first several lines of a text file before importing it. Identify the delimiter, header row, encoding, and whether every record follows the same structure.

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.

Text File OrderID,Product,Sales 1001,Laptop,120000 1002,Monitor,45000 1003,Mouse,6400 Parse and Validate Detect delimiter Identify headers Check values Review data types Power BI Table OrderID | Product | Sales 1001 | Laptop | 120000 1002 | Monitor | 45000 Always verify the parsed result before loading.

Structured text data is interpreted and checked before it becomes a model table.

4. Importing a Text File

Step-by-Step Procedure

  1. Open Power BI Desktop.
  2. Select the Home ribbon.
  3. Choose Get data.
  4. Select the appropriate text or CSV connector.
  5. Browse to the file you want to import.
  6. Review the preview and confirm that the fields are separated correctly.
  7. Check the first row to determine whether it contains column headers.
  8. Verify that special characters and text values display correctly.
  9. Select Transform Data if cleaning or conversion is required.
  10. 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.
Important: A folder connection is not the same as importing one file. Review the listed files and filter out unrelated content, such as temporary files, backups, or files with incompatible structures.

6. How Folder Import Works

Source Folder Sales_January.csv Monthly records Sales_February.csv Monthly records Sales_March.csv Monthly records Folder Connector Inspect and filter Combine compatible files Combined Dataset OrderID | Product | Sales January records February and March records Compatible files can be combined into one query.

Conceptual folder workflow for combining files with compatible structures.

7. Connecting Power BI to a Folder

Step-by-Step Procedure

  1. Place the related source files in a dedicated folder.
  2. Check that the files have compatible column names and structures.
  3. Open Power BI Desktop.
  4. Select Home → Get data.
  5. Choose the Folder connector from the available data sources.
  6. Browse to the folder containing your files.
  7. Confirm the selected folder path.
  8. Review the file list in the folder preview.
  9. Filter out files that should not be included.
  10. Choose Combine or Combine and Transform Data when the option is available and appropriate.
  11. Review the sample file and the generated transformation steps.
  12. Verify the combined rows and columns before applying the query.
Practical Tip: Keep unrelated documents, archived exports, and backup copies outside the folder used by the query. A folder query may include files you did not intend to process unless you filter them.

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.

Folder File Preview Name Extension Include? Reason Sales_January.csv .csv Yes Expected data file Sales_February.csv .csv Yes Expected data file Sales_Backup.xlsx .xlsx No Different format ReadMe.txt .txt No Not part of dataset Example only: filter files according to your actual source requirements.

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.
Important: Filtering by file extension alone does not guarantee that every included file has the same columns or data structure.

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.
Remember: Combining monthly files is usually a row-stacking task, which corresponds to appending data. Merging is used when you need to match related records across tables.

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.

Source Folder January and February Add March file Refresh Query Re-read matching files Apply transformations Updated Dataset January, February, and March records Refresh only works as expected when the source remains accessible New files must satisfy the query's filters and structure requirements.

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.
Data Quality Warning: Do not remove records simply because they look unusual. First determine whether they are invalid, incomplete, duplicated, or legitimate business records.

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

  1. Create a CSV file named Attendance.csv.
  2. Add StudentID, StudentName, AttendanceDate, and Status columns.
  3. Enter at least five sample records.
  4. Import the file into Power BI Desktop.
  5. Verify the delimiter and header row.
  6. Check the date and text data types.
  7. Open Power Query and correct any problems.
  8. Apply the changes and inspect the result.

Assignment B: Combine Monthly Sales Files

  1. Create a folder named MonthlySales.
  2. Create three CSV files named Sales_January.csv, Sales_February.csv, and Sales_March.csv.
  3. Give all three files the same column names and compatible data types.
  4. Place the files in the MonthlySales folder.
  5. Connect Power BI Desktop to that folder.
  6. Review the file list and exclude unrelated files.
  7. Use the combine option when appropriate.
  8. Inspect the sample file and generated transformation steps.
  9. Verify that the combined table contains records from all intended files.
  10. 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.