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 Quality Validation

Learn how to validate data quality in Power BI, identify incomplete or inconsistent records, verify data types, check business rules, and prepare trustworthy datasets for reporting and analysis.

1. Introduction to Data Quality Validation

Data quality validation is the process of checking whether data is complete, accurate, consistent, valid, and suitable for its intended purpose. It helps identify problems before data is used in reports, dashboards, or business decisions.

Power Query provides data profiling, filtering, transformation, and error-handling features that can help validate data during the preparation stage.

Key Data Quality Dimensions

  • Completeness: Required fields contain values.
  • Accuracy: Values correctly represent the source information.
  • Consistency: Similar values follow the same rules and formats.
  • Validity: Values follow defined data types and business rules.
  • Uniqueness: Records that must be unique do not contain unwanted duplicates.
  • Timeliness: Data is sufficiently current for its intended use.
Completeness Required values exist Accuracy Values are correct Consistency Values follow common rules Validity Values meet defined rules Uniqueness Unwanted duplicates absent Timeliness Data is current enough Together, these dimensions help assess data fitness for use.

Visual 1: Important dimensions of data quality.

2. Validate Data Using Column Quality

Power Query's column quality feature helps identify valid values, errors, and empty values. It is a useful first step when investigating a newly imported dataset.

Steps to Inspect Column Quality

  1. Open Power BI Desktop.
  2. Select Home > Transform data.
  3. Select the query you want to inspect.
  4. Open the View tab in Power Query Editor.
  5. Enable Column quality.
  6. Review the indicators displayed above the data preview.
  7. Investigate columns with errors or empty values.
Column Quality Overview Customer Quantity OrderDate Valid Empty Error Illustrative values only; actual percentages depend on the dataset.

Visual 2: Column quality can help identify columns requiring attention.

Tip: Check whether profiling is based on the top 1,000 rows or the entire dataset. The selected profiling setting affects how you interpret the indicators.

3. Validate Data Types and Required Fields

Each column should use a data type suitable for its contents. Numeric values should not contain unexplained text, dates should represent valid dates, and required identifiers should be checked for missing values.

Column Validation Rule Example of a Problem
OrderID Must not be null; uniqueness depends on the business key. A missing identifier.
Quantity Must be numeric and follow the quantity rules. Text such as "abc".
UnitPrice Must be numeric and within an acceptable range. An unexplained negative price.
OrderDate Must be a valid date when required. An invalid date string.
Email Must follow the required format if provided. A value without an expected email structure.

Validation Checklist

  • Confirm each column's data type.
  • Identify null values in required fields.
  • Check for conversion errors.
  • Check identifiers for unwanted duplicates.
  • Review numeric values against expected ranges.
  • Verify that dates follow the correct source format.

4. Validate Business Rules

A value can have a valid data type and still violate a business rule. For example, a negative quantity may be invalid for an ordinary sales order but valid for a return transaction. Validation must therefore consider the context of the data.

Business Rule Validation Input Record Quantity = -5 Apply Rule Check transaction type Investigate Return or invalid value? Valid Under the Rule Retain the record Rule Violation Flag, correct, or investigate

Visual 3: Apply business context before deciding whether a value is invalid.

Examples of Business Rules

  • OrderID must be present for every sales record.
  • Quantity must follow the rules for sales and returns.
  • UnitPrice must fall within an approved range.
  • DeliveryDate must not precede OrderDate unless the source rules explain the case.
  • A customer identifier must match a valid customer record.
Important: Document the rule before flagging records as invalid. A rule that is appropriate for one business process may not be appropriate for another.

5. Create Validation Columns in Power Query

A conditional column or custom column can classify records according to a defined rule. For example, a sales dataset can flag rows with missing order identifiers or invalid quantities.

Example: Quantity Validation

Assume Quantity is numeric and the rule requires a non-negative value. The following M expression illustrates how to classify records when nulls are also possible.

if [Quantity] = null then "Missing Quantity" else if [Quantity] < 0 then "Review Quantity" else "Valid"

In Power Query, you can create a custom column from the Add Column tab and adapt the expression to your actual column names and business requirements.

Validation Status Valid Value meets the rule Review Value needs investigation Missing Required value is absent A validation column makes exceptions easier to filter and review. These labels are examples; define rules for your own dataset.

Visual 4: Validation categories can help separate valid records from exceptions.

Tip: Keep validation results in a separate status column when possible. This makes exceptions visible without immediately deleting the original records.

6. Validate Relationships and Reference Data

Data quality also depends on how tables relate to each other. For example, every non-null ProductID in a sales table may be expected to match a ProductID in the Products table.

A value that does not match a reference table may indicate a typo, an outdated identifier, a missing lookup record, or an expected business exception.

Reference Validation Checklist

  • Check that key columns use compatible data types.
  • Look for null values in required key fields.
  • Identify keys in the fact table that do not exist in the lookup table.
  • Check whether the lookup table's key is unique when required.
  • Investigate unmatched records before removing them.
Product Reference Validation Sales Table ProductID: P101 ProductID: P102 ProductID: P999 Match keys Products Table ProductID: P101 ProductID: P102 P999 not found Investigate unmatched keys before deciding how to handle them.

Visual 5: Compare identifiers with a reference table to find unmatched records.

7. Practical Exercise: Sales Data Validation

Dataset Columns

Use a sales dataset containing OrderID, ProductID, Quantity, UnitPrice, OrderDate, and CustomerID.

Practice Tasks

  1. Check required fields for missing values.
  2. Confirm that Quantity and UnitPrice have appropriate data types.
  3. Inspect the dataset for duplicate order records.
  4. Check Quantity against the rules for sales and returns.
  5. Inspect OrderDate for invalid dates.
  6. Compare ProductID values with the Products table.
  7. Create a validation status column for records requiring review.
  8. Record the number of valid, invalid, and missing-value records.
  9. Correct confirmed errors and document unresolved exceptions.

Expected outcome: A validated dataset with clearly identified exceptions and documented data-quality rules.

8. Knowledge Check

Question 1: What is data quality validation?

A. Changing report colors

B. Checking whether data meets defined requirements

C. Creating a new dashboard page

D. Publishing a report without reviewing it


Question 2: What can Column quality help identify?

A. Valid values, empty values, and errors

B. Only report titles

C. Only visual formatting

D. Only dashboard permissions


Question 3: Why are business rules important?

A. Every negative value is always invalid

B. They determine whether values are acceptable in context

C. They automatically remove all errors

D. They replace the need for data types


Question 4: What should you do with an unmatched ProductID?

A. Delete every sales record

B. Replace it with a random identifier

C. Investigate the mismatch and follow the data-quality rules

D. Ignore all reference tables

View Answers
  1. B — Checking whether data meets defined requirements.
  2. A — Valid values, empty values, and errors.
  3. B — Business rules determine whether values are acceptable in context.
  4. C — Investigate the mismatch and follow the rules.

Summary

In this lesson, you learned how to validate data completeness, accuracy, consistency, validity, uniqueness, and timeliness. You explored column quality indicators, data type checks, required-field validation, and business-rule checks.

You also learned how to create validation status columns and compare identifiers with reference tables. Apply documented rules, investigate exceptions, and verify the final dataset before using it in Power BI reports.