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

Data Cleaning and Modeling Assignment

This assignment focuses on preparing reliable data for analysis in Power BI. You will use Power Query to clean source data, organize tables, create relationships, and validate the model before building reports.

Assignment Scenario

The reporting team at CIIT TRANING INSTITUTE has received sales data from several departments. The files contain inconsistent product names, missing values, duplicate records, incorrect data types, and repeated customer information.

Your task is to prepare the data and create a model that can support reliable sales analysis by product, customer, region, and date.

1. Assignment Objectives

  • Identify common data quality problems.
  • Import source files into Power BI Desktop.
  • Clean and transform data using Power Query.
  • Set suitable data types and standardize values.
  • Separate fact data from dimension data.
  • Create and validate table relationships.
  • Test the model using simple measures and visuals.
  • Document the transformations performed.

Data Preparation Lifecycle

Source Files Excel / CSV Clean Power Query Model Relationships Validate Test results

2. Understand the Sample Data

Use a sales transaction table similar to the example below. You can prepare your own workbook with these columns or use an existing practice dataset.

OrderID OrderDate Product Region Quantity UnitPrice
ORD001 2026-01-05 Laptop West 2 45000
ORD002 2026-01-08 Monitor East 3 12000
ORD003 2026-02-02 Keyboard North 5 1500
ORD004 2026-02-10 Laptop West 1 45000
Important: The sample rows are illustrative. In a real assignment, use the supplied source file and confirm its meaning before removing or changing any records.

3. Identify Data Quality Problems

Before changing data, inspect it and identify the issues that may affect calculations or reporting.

Problem Example Possible action
Inconsistent text West, west, WEST Standardize case and whitespace.
Missing values Blank Product Investigate the source and decide how to handle it.
Incorrect data type Quantity stored as text Convert to a numeric type after checking values.
Duplicate rows The same transaction appears twice Confirm whether it is a true duplicate before removing it.
Invalid date 31/02/2026 Investigate and correct from a reliable source if possible.
Unexpected numeric values Negative quantity Check whether it represents a return or an error.

Common Data Quality Issues

Inconsistent Text West west WEST One value, many forms Missing Values Product Blank Quantity Blank Meaning must be checked Wrong Data Type "45000" as text 45000 as number Validate before conversion

4. Import Data into Power Query

  1. Open Power BI Desktop.
  2. Select Home → Get data.
  3. Choose Excel, Text/CSV, or the connector matching your source.
  4. Select the required table or worksheet.
  5. Choose Transform Data.
  6. Inspect the columns, data types, null values, and row counts.

5. Clean and Transform the Data

Step 1: Rename Columns

Use clear, consistent column names such as OrderID, OrderDate, Product, Region, Quantity, and UnitPrice. Avoid unnecessary spaces and ambiguous abbreviations.

Step 2: Remove Extra Spaces

Select text columns and use the Power Query transformation options to apply Trim and, where appropriate, Clean. These operations can remove unwanted spaces and non-printing characters.

Step 3: Standardize Text

Use the Transform ribbon to change text case where appropriate. For business categories such as Region, verify that the standardized values represent the same category before replacing them.

Step 4: Set Data Types

Column Recommended type
OrderIDText
OrderDateDate
ProductText
RegionText
QuantityWhole Number
UnitPriceFixed Decimal Number or Decimal Number

Step 5: Review Nulls and Errors

Filter for blank values and error rows. Investigate why each value is missing or invalid before deciding whether to replace it, retain it, or exclude the affected record.

Step 6: Review Duplicates

Select the correct business key or set of columns before using Remove Duplicates. Two rows with the same customer or product are not necessarily duplicate transactions.

Do not remove records blindly. Returns, adjustments, repeated line items, and legitimate repeated purchases may look similar to duplicate records. Verify the business meaning first.

Cleaning Sequence

Inspect Profile columns Transform Text and types Investigate Nulls and duplicates Validate Check results Close & Apply

6. Separate Fact and Dimension Tables

A data model is easier to maintain when transaction records are separated from descriptive information.

Table Example columns Purpose
FactSales OrderID, DateKey, ProductKey, CustomerKey, Quantity, UnitPrice Stores transaction-level values.
DimProduct ProductKey, ProductName, Category Describes products.
DimCustomer CustomerKey, CustomerName, Segment Describes customers.
DimDate DateKey, Date, Month, Year Supports time-based analysis.

Example Star Schema

FactSales OrderID, DateKey ProductKey, CustomerKey Quantity, UnitPrice DimProduct Product, Category DimDate Date, Month, Year DimCustomer Customer, Segment Other Dimensions As required

7. Create Relationships

  1. Open Model view in Power BI Desktop.
  2. Identify the key column shared by each fact and dimension table.
  3. Create a one-to-many relationship from the dimension table to the fact table where appropriate.
  4. Confirm that dimension keys are unique.
  5. Review relationship direction and active status.
  6. Test whether filters from dimensions correctly affect the fact table.
Modeling tip: Prefer a clear star schema for typical analytical reporting. Avoid unnecessary many-to-many relationships and bidirectional filtering unless the model genuinely requires them.

8. Create Validation Measures

After cleaning and modeling the data, create a few measures to test whether the model returns sensible results.

Total Quantity

Total Quantity = SUM(FactSales[Quantity])

Total Sales

Total Sales = SUMX( FactSales, FactSales[Quantity] * FactSales[UnitPrice] )

Transaction Rows

Transaction Rows = COUNTROWS(FactSales)

Distinct Orders

Distinct Orders = DISTINCTCOUNT(FactSales[OrderID])

Use these measures in cards and tables. Compare their results with independent calculations from the source file and investigate any differences.

9. Test the Data Model

  • Compare row counts before and after cleaning.
  • Check that excluded rows are documented.
  • Verify that date values sort chronologically.
  • Compare Total Sales against a trusted source total.
  • Check whether filtering by product changes sales as expected.
  • Verify that distinct orders are not confused with transaction rows.
  • Look for blank categories and unexpected totals.
  • Record any remaining data limitations.

Validation Checklist

Before building reports Key columns contain valid values Dimension keys are unique Relationships filter the correct rows Measures reconcile with trusted totals

10. Practical Assignment Tasks

Complete the Data Cleaning and Modeling Assignment

  1. Import the provided sales dataset into Power BI Desktop.
  2. List at least five data quality problems found in the source data.
  3. Clean text values and standardize column names.
  4. Correct data types and investigate null values and errors.
  5. Review duplicate records using the appropriate business key.
  6. Separate the transaction table from relevant dimension tables.
  7. Create relationships in Model view.
  8. Create Total Quantity, Total Sales, Transaction Rows, and Distinct Orders measures.
  9. Build a test page with cards and a product or region table.
  10. Validate the results and document all major transformations.

Required Submission

  • Power BI file (.pbix).
  • Screenshot of the final data model.
  • List of data quality problems discovered.
  • Summary of Power Query transformations.
  • Screenshot of validation measures or visuals.
  • Short explanation of relationship design decisions.

11. Knowledge Check

Question 1: What is the purpose of Power Query?

Power Query is used to connect to data sources and transform data through operations such as filtering, renaming, changing data types, and handling missing or inconsistent values.

Question 2: Should every repeated row be removed?

No. Confirm whether the rows are true duplicates according to the business rules. Similar-looking transactions may be legitimate.

Question 3: What is a fact table?

A fact table typically stores business events or transactions and numeric values that can be analyzed, such as quantities and sales.

Question 4: Why should dimension keys be unique?

Unique dimension keys support the expected one-to-many relationship and help prevent ambiguous or incorrect filtering.

Question 5: How can you check whether a model is correct?

Compare row counts and key totals with the source data, test relationships using filters, and investigate unexpected results.

Summary

In this assignment, you practiced preparing data for Power BI analysis. You learned to identify data quality problems, transform source files with Power Query, separate fact and dimension tables, create relationships, and validate calculations.

  • Inspect data before making changes.
  • Standardize values and correct data types.
  • Investigate missing values and duplicates before removing them.
  • Build a clear model with appropriate relationships.
  • Validate measures against trusted source totals.
  • Document transformations and assumptions.

Reliable reports begin with well-prepared data and a model whose relationships and calculations have been tested.