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

Remove Duplicates and Errors

Learn how to identify duplicate records, remove unwanted duplicates, detect data errors, and improve data reliability using Power Query in Power BI.

1. Introduction to Duplicates and Errors

Duplicate records occur when the same record or key value appears more than once. Data errors occur when a value cannot be interpreted or processed as expected, such as text in a numeric column or an invalid date.

Why Data Cleaning Matters

  • Duplicates can inflate counts and totals.
  • Incorrect data types can cause calculation errors.
  • Invalid values can reduce report accuracy.
  • Consistent records improve analysis and reporting.
  • Data validation makes problems easier to identify.
Duplicate Records OrderID Product Amount 101 Laptop 50000 101 Laptop 50000 The same record appears twice. Data Errors Product Quantity Price Monitor abc 12000 Text appears in a numeric field. Check values before conversion.

Visual 1: Examples of duplicate records and incompatible values.

2. Understand Duplicate Records

A duplicate depends on the rule you use to identify it. Two rows may be identical across every column, or they may share the same key while containing different values in other columns.

OrderID Customer Amount Observation
101 Samiksha 2500 First record
101 Samiksha 2500 Exact duplicate of the first record
102 Omkar 1800 Different order
103 Pradnya 3200 Different order
Important: Decide which columns define a duplicate before removing records. Two orders with the same customer are not necessarily duplicates.

3. Remove Duplicates in Power Query

  1. Open Power BI Desktop.
  2. Select Home > Transform data.
  3. Select the required query.
  4. Select the column or columns used to identify duplicates.
  5. Open the Home or Transform tab.
  6. Choose Remove Rows > Remove Duplicates, or use the available column context menu.
  7. Review the result and verify the remaining row count.
Remove Duplicates Workflow Select Key Columns Choose Remove Duplicates Inspect Remaining Rows Validate Data Accuracy Choose the correct duplicate definition before removing rows. Check row counts and important business keys.

Visual 2: A practical workflow for removing duplicates.

Note: If several rows share the selected key but contain different information, review the business rules first. Do not assume that every repeated key is an unwanted duplicate.

4. Understand Data Errors

Power Query errors can occur when a transformation cannot process a value as expected. For example, converting the text "abc" into a whole number can produce an error.

Error Type Possible Cause Suggested Action
Number conversion Text appears in a numeric column. Inspect the value and correct the source or conversion rule.
Date conversion Invalid date text or incorrect locale settings. Check the date format and locale.
Missing column A source column was renamed or removed. Review the affected transformation step.
Expression error A formula uses invalid values or references. Inspect the formula and its input fields.
Source error A source path or connection is unavailable. Check connection settings and permissions.

5. Find and Handle Errors

Before removing errors, determine why they occurred. An error may reveal a genuine source-data problem or an incorrect transformation that should be fixed instead.

  1. Open the query in Power Query Editor.
  2. Inspect the column quality indicators when available.
  3. Locate cells displaying errors in the data preview.
  4. Select an error cell to inspect its details.
  5. Correct the source value, transformation, or data type where possible.
  6. If removal is justified, select the relevant column and use Remove Errors.
  7. Review the remaining rows and confirm that important records were not lost.
Error Investigation Detect Find error cells Investigate Find the cause Correct Fix the value Validate Check results Remove errors only when removal is appropriate. Correcting a value is often better than discarding a record.

Visual 3: A responsible approach to handling data errors.

Tip: Use Replace Errors only when you know which replacement is appropriate. A replacement should follow the meaning of the column and the business rules.

6. Example: Correct an Invalid Quantity

Consider a table where Quantity is expected to contain whole numbers, but one record contains the text "abc".

OrderID Product Quantity Observation
201 Laptop 2 Valid numeric value
202 Monitor abc Invalid for a whole-number field
203 Keyboard 5 Valid numeric value

Recommended Steps

  1. Inspect the original source record for OrderID 202.
  2. Determine the correct quantity from a trusted source.
  3. Correct the value if reliable information is available.
  4. If the value cannot be recovered, apply the approved data-quality rule.
  5. Validate the resulting column and row count.

7. Practical Exercise

Exercise: Sales Data Quality

Import a sales table containing OrderID, Product, Quantity, UnitPrice, and OrderDate.

  1. Find exact duplicate rows.
  2. Determine whether OrderID should be unique.
  3. Check Quantity for non-numeric values.
  4. Check UnitPrice for conversion errors.
  5. Check OrderDate for invalid dates.
  6. Correct errors when reliable source information exists.
  7. Remove duplicates only according to documented rules.
  8. Compare row counts before and after cleaning.

Expected outcome: A cleaned sales query with duplicate handling and error corrections documented.

8. Knowledge Check

Question 1: What is a duplicate record?

A. A record with a missing date only

B. A record repeated according to the selected duplicate criteria

C. A report page

D. A data source connection


Question 2: What should you do before removing duplicates?

A. Choose the columns that define a duplicate

B. Delete every repeated customer

C. Change all values to text

D. Remove all filters


Question 3: What may cause a number conversion error?

A. A valid integer

B. A report title

C. Text such as "abc" in a numeric field

D. A table border


Question 4: What is a good first response to a data error?

A. Always delete the entire query

B. Investigate the cause and inspect the affected value

C. Ignore every error

D. Publish without checking

View Answers
  1. B — A record repeated according to the selected criteria.
  2. A — Choose the columns that define a duplicate.
  3. C — Text in a numeric field can cause conversion errors.
  4. B — Investigate the cause and inspect the value.

Summary

In this lesson, you learned how to identify duplicate records, select appropriate duplicate criteria, and remove unwanted duplicates using Power Query.

You also learned how data errors occur, how to investigate invalid values, and why correcting an error is often preferable to removing an entire record. Always validate the final dataset before loading it into Power BI.