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

Missing and Null Values

Learn how to identify missing data, understand null values, choose suitable cleaning methods, and prepare reliable datasets using Power Query in Power BI.

1. Introduction to Missing and Null Values

Missing data occurs when a field does not contain an expected value. In Power Query, a null value represents the absence of a value. Missing data can occur because information was not collected, a source field was empty, or records were imported incorrectly.

Missing values should be investigated before they are removed or replaced. The correct treatment depends on the meaning of the column and the requirements of the business.

Common Examples

  • A customer record has no phone number.
  • An order has no delivery date because it is still pending.
  • A sales row has no quantity value.
  • A product has no assigned category.
  • A source file contains blank cells in required fields.
Customer Records Customer Phone City Samiksha 9876543210 Pune Omkar null Mumbai Pradnya 9123456780 null Highlighted cells contain missing values.

Visual 1: Missing values can occur in different columns.

2. Blank, Null, and Placeholder Values

Not every value that looks empty is stored in the same way. A null, an empty text string, a cell containing spaces, and a placeholder such as "N/A" may require different cleaning steps.

Value Meaning Possible Treatment
null No value is present. Replace, preserve, or remove based on the field.
Empty text A text value may contain zero characters. Replace with null or a suitable value if appropriate.
Spaces A text field may contain whitespace only. Trim the text and check for empty results.
N/A A placeholder entered by the source system or user. Standardize it according to the data rules.
0 A numeric value that may be valid. Keep it unless the business rules define it as missing.
null No value Missing field "" Empty text Zero characters "N/A" Placeholder Text value 0 Numeric zero May be valid Inspect the actual data before deciding what to replace.

Visual 2: Different empty-looking values may have different meanings.

Important: Do not replace every zero with null. A zero quantity, zero balance, or zero sales amount may be a legitimate business value.

3. Identify Missing Values in Power Query

Use the data preview and column quality indicators to investigate missing values. Depending on the Power Query version and enabled profiling options, column quality can display valid, error, and empty values.

  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 if it is not already enabled.
  6. Review columns with empty or error values.
  7. Check the data preview and source records before making changes.
Column Quality Customer Phone Revenue Valid Empty Error Illustrative indicator only; actual proportions depend on your data.

Visual 3: Column quality can help locate columns requiring investigation.

Tip: Data profiling may be based on the top 1,000 rows or the entire dataset, depending on your selected profiling setting. Check the setting when interpreting results.

4. Choose a Suitable Cleaning Method

There is no single correct treatment for all missing values. Choose a method based on what the missing value means and how the cleaned dataset will be used.

Method When to Consider It Example
Remove rows When the record is unusable and removal is justified. Remove a row missing a required transaction key.
Replace with a value When a valid replacement is known or defined by policy. Replace a missing category with "Unassigned".
Fill Down When blank cells should inherit the previous value. Fill a repeated department label in grouped rows.
Fill Up When blank cells should inherit the next value. Fill a repeated label from the following row.
Keep null When missingness itself carries useful information. Keep an empty delivery date for a pending order.
Missing Value Found Investigate the meaning Replacement Is Justified? Use a documented replacement rule Value Cannot Be Inferred? Preserve or investigate further Replace, fill, or remove appropriately Keep null or request clarification

Visual 4: Select a cleaning method based on the meaning of the data.

5. Remove or Replace Null Values

Remove Rows with Missing Values

  1. Select the relevant column in Power Query Editor.
  2. Open its filter menu.
  3. Clear the null or blank option when appropriate.
  4. Apply the filter.
  5. Check how many rows remain and verify the business impact.

Replace Values

  1. Select the column that requires cleaning.
  2. Use Replace Values where the value can be targeted reliably.
  3. Specify the replacement according to your data rules.
  4. Review the resulting values and data type.
Important: Replacing null with zero can change averages, totals, and other calculations. Use zero only when it represents the intended business meaning.

6. Example: Clean a Sales Dataset

Consider the following dataset. Some records contain missing quantities or product names.

OrderID Product Quantity UnitPrice
101 Laptop 2 50000
102 Monitor null 12000
103 null 3 800
104 Keyboard 5 700

Suggested Investigation

  • Order 101 contains complete values.
  • Order 102 needs quantity verification before calculating revenue.
  • Order 103 needs product identification or an approved missing-category rule.
  • Order 104 contains complete values.

Do not automatically delete orders 102 and 103. First determine whether the missing information can be recovered from the source or handled according to business rules.

Before Cleaning Order 101: Complete Order 102: Quantity missing Order 103: Product missing Order 104: Complete Needs investigation After Review Order 101: Retain Order 102: Verify quantity Order 103: Identify product Order 104: Retain Apply documented rules

Visual 5: Review missing values before choosing a final treatment.

7. Practical Exercise

Exercise: Customer Data Cleaning

Import a customer table containing CustomerID, CustomerName, Phone, City, and Email.

  1. Identify columns containing null and blank values.
  2. Inspect whether blank text and null values are different.
  3. Check whether missing CustomerID values make records unusable.
  4. Decide whether missing Phone and Email values should be retained.
  5. Standardize placeholder values such as N/A when appropriate.
  6. Document the cleaning rules you applied.
  7. Compare row counts before and after cleaning.

Expected outcome: A dataset with investigated missing values and clearly documented cleaning decisions.

8. Knowledge Check

Question 1: What does null generally represent?

A. A guaranteed numeric zero

B. The absence of a value

C. A duplicate record

D. A column heading


Question 2: What should you do before removing missing records?

A. Delete all columns

B. Change every value to zero

C. Investigate the meaning and impact of missing values

D. Publish the report immediately


Question 3: Is zero always a missing value?

A. Yes

B. No, it may be a valid numeric value

C. Only in text columns

D. Only in date columns


Question 4: Which feature can help identify empty values?

A. Column quality

B. Report themes

C. Page navigation

D. Visual borders

View Answers
  1. B — The absence of a value.
  2. C — Investigate the meaning and impact.
  3. B — Zero may be valid.
  4. A — Column quality.

Summary

Missing values can appear as null, blank text, spaces, or placeholders. Column quality helps identify data that requires investigation. Missing values may be removed, replaced, filled, or preserved. Zero should not automatically be treated as missing. Cleaning decisions should follow documented business rules. Validate row counts and results after cleaning.

You have completed the lesson on Missing and Null Values. Continue to the next lesson to learn how to remove duplicates and handle errors in Power Query.