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

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.
Practical Tip: Whenever possible, organize Excel source data as a clean table with one header row, consistent columns, and no unnecessary blank rows or merged cells.

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.

SalesData OrderID Date 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 One header row, consistent columns, and one record per row

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

  1. Open Power BI Desktop.
  2. Go to the Home ribbon.
  3. Select Get data.
  4. Choose Excel workbook. If it is not immediately visible, open the available data-source list.
  5. Browse to the location of your Excel file.
  6. Select the workbook and choose Open.
  7. Wait for the Navigator window to display the available workbook items.
  8. Select the required worksheet or Excel table.
  9. Review the preview to verify that the correct data is selected.
  10. Choose Load to import directly, or Transform Data to inspect and prepare the data first.
Step 1 Home → Get data Select Excel workbook Step 2 Browse and select The workbook file Step 3 Select worksheet Or Excel table Step 4 Review the preview Verify headers and values Step 5 Load or Transform Data Prepare the dataset

Typical workflow for connecting an Excel workbook to Power BI Desktop.

Best Practice: When an Excel workbook contains a properly named Excel table, selecting that table can make the source easier to identify and maintain than selecting a large worksheet range.

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

  1. Read the worksheet or table names carefully.
  2. Select the item containing the required records.
  3. Inspect the column names and sample values.
  4. Confirm that the preview does not contain unrelated notes or blank sections.
  5. Choose Load or Transform Data based on the condition of the data.
Common Mistake: Selecting a worksheet with the correct name does not guarantee that its layout is suitable for reporting. Always inspect the preview before importing it.

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

  1. Open Power BI Desktop.
  2. Select Home → Get data.
  3. Choose the Text/CSV connector.
  4. Browse to the required CSV file.
  5. Select the file and open it.
  6. Review the detected delimiter and the previewed columns.
  7. Check whether the first row contains the intended column headers.
  8. Verify that values appear in the correct columns.
  9. Select Load or Transform Data.
  10. After loading, inspect the table in Data view or Model view as appropriate.
CSV Text OrderID,Product,Sales 1001,Laptop,120000 1002,Monitor,45000 1003,Mouse,6400 Parse Data Detect delimiter Split into columns Interpret values Preview the result Structured Table OrderID | Product | Sales 1001 | Laptop | 120000 1002 | Monitor | 45000 Validate parsing before loading the CSV into the model.

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.

Important: Do not assume every CSV file uses commas. Check the actual file contents and confirm the detected delimiter in the preview.

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.

Source Preview Excel or CSV Choose an Action Load → Import Data Transform Data → Power Query

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.
Recommended Workflow: For unfamiliar or messy source files, choose Transform Data first. Review the data in Power Query and apply the necessary cleaning steps before loading it into the model.

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.

Remember: Changing a data type does not automatically fix every data-quality problem. Invalid dates, inconsistent number formats, and non-numeric text can still cause conversion errors.

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

  1. Create an Excel workbook with the sample data above.
  2. Save the workbook as SalesData.xlsx.
  3. Format the dataset as an Excel table if appropriate.
  4. Open Power BI Desktop and select Get data → Excel workbook.
  5. Select the workbook and choose the sales table or worksheet.
  6. Review the preview and choose Transform Data.
  7. Confirm that the column names are correct.
  8. Set OrderDate to Date, Quantity to Whole Number, and Sales to a suitable numeric type.
  9. Check for blank rows and conversion errors.
  10. Select Close & Apply to apply the Power Query changes.
  11. 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.
Important Rule: Never remove duplicates or replace missing values automatically without understanding the business meaning of the data. A repeated order line may be valid, and a blank value may represent unknown information rather than zero.

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.
Refresh Note: Importing a file does not mean Power BI automatically reads every future change to that file. Refresh the data when needed and configure the appropriate refresh process for your environment.

13. Hands-On Practice Assignment

Assignment A: Excel Import

  1. Create an Excel workbook named StudentPerformance.xlsx.
  2. Create columns for StudentID, StudentName, Course, TestScore, and TestDate.
  3. Enter at least five sample student records.
  4. Save the workbook.
  5. Import it into Power BI Desktop using the Excel workbook connector.
  6. Review the Navigator preview.
  7. Open Power Query and verify the column names.
  8. Set TestScore to a suitable numeric type and TestDate to Date.
  9. Apply the changes and verify the imported table.

Assignment B: CSV Import

  1. Create a CSV file named StudentPerformance.csv with the same fields.
  2. Save the file using a consistent delimiter.
  3. Import the file using the Text/CSV connector.
  4. Verify the delimiter and header row.
  5. Check that the student names appear correctly.
  6. Review the detected data types.
  7. Correct any issues in Power Query.
  8. Load the cleaned dataset into Power BI.
  9. 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.