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

Replace and Fill Values

Learn how to replace incorrect values, standardize inconsistent entries, fill missing values, and prepare reliable datasets using Power Query in Power BI.

1. Introduction to Replace and Fill Values

Data imported from spreadsheets, databases, or external systems may contain missing values, inconsistent labels, or values that need standardization. Power Query provides transformation tools to replace values and fill data where appropriate.

Common Use Cases

  • Replace an abbreviation with a standard category name.
  • Replace a known placeholder with null.
  • Fill down repeated group labels in structured data.
  • Fill up values when the following row provides the correct label.
  • Replace errors when an approved replacement rule exists.
  • Standardize inconsistent text values before reporting.
Replace and Fill Workflow Source Data Inconsistent values Inspect Understand meaning Transform Replace or fill Validate Check output Choose a transformation based on the meaning of the data.

Visual 1: A general workflow for replacing and filling values.

2. Replace Values in Power Query

Replace Values lets you substitute one value for another in a selected column. It is useful for standardizing known variations and correcting values when the replacement is reliable.

Steps to Replace Values

  1. Open Power BI Desktop.
  2. Select Home > Transform data.
  3. Select the required query.
  4. Select the column you want to modify.
  5. Choose Replace Values from the available transformation options or column context menu.
  6. Enter the value to find and the replacement value.
  7. Confirm the operation and inspect the results.
Original Value Replacement Reason
Mum. Mumbai Standardize a known abbreviation.
PUNE Pune Standardize capitalization when required.
N/A null Represent a placeholder as missing data when appropriate.
Pending Pending Keep a meaningful status unchanged.
City Standardization Before After Mum. Mumbai PUNE Pune N/A null, if defined by the cleaning rule Confirm the meaning of each replacement before applying it.

Visual 2: Example of replacing inconsistent values.

Important: A replacement that looks reasonable may still change the meaning of a record. Confirm the correct value with the source or an approved business rule.

3. Fill Down and Fill Up

Fill Down copies the last non-null value downward into subsequent null cells. Fill Up copies a value upward from the next non-null cell. These operations are useful when the structure of the source data indicates that blank cells belong to a repeated group.

Department Employee
Sales Samiksha
null Omkar
null Pradnya
Support Neha
null Samadhan

If the source format indicates that each blank department belongs to the preceding department, Fill Down can populate the missing department labels.

Fill Down Before After Sales null null Support Sales Sales Sales Support Fill Down is appropriate only when blank cells inherit the previous label.

Visual 3: Fill Down carries the previous non-null value into null rows.

How to Apply Fill Down

  1. Select the column containing the null values.
  2. Open the Transform tab.
  3. Choose Fill > Down.
  4. Inspect the populated cells.
  5. Confirm that the filled values match the source structure.

How to Apply Fill Up

  1. Select the required column.
  2. Open the Transform tab.
  3. Choose Fill > Up.
  4. Inspect the resulting values and verify their meaning.
Warning: Fill Down and Fill Up can introduce incorrect information if blank cells do not represent repeated labels. Sort and inspect the source data before using these operations.

4. Replace Null Values Carefully

Null values can represent information that was not provided, information that is not applicable, or information that is not yet available. The correct replacement depends on the column and its business meaning.

Column Possible Treatment Reason
Department Use "Unassigned" when approved. Clearly identifies records without a department.
Quantity Investigate before replacing. A missing quantity is not necessarily zero.
DeliveryDate Keep null if delivery is pending. A date should not be invented.
OptionalComment Keep null or use an approved label. The field may be genuinely optional.
ProductCategory Use a documented fallback category. Helps group unknown categories transparently.
Best practice: Keep the original data available when possible, document replacement rules, and validate the results after each transformation.

5. Replace Errors

Replace Errors can substitute error values with a defined replacement. Before using it, inspect the error and decide whether the value can be corrected from a reliable source.

  1. Select the column containing errors.
  2. Inspect the error details to understand the cause.
  3. Choose Replace Errors if a replacement is justified.
  4. Enter the approved replacement value.
  5. Verify the resulting values and data type.
Replace Errors Responsibly Error Found Inspect the details Check Cause Verify source value Apply Rule Correct or replace Do not hide a data-quality problem with an arbitrary value. Validate the cleaned data before loading it.

Visual 4: Investigate an error before replacing it.

6. Example: Clean Department Data

Consider the following table where department labels are repeated only at the start of each group.

Department Employee Status
Sales Samiksha Active
null Omkar Active
null Pradnya Inactive
Support Neha Active
null Samadhan Active

If the source layout confirms that blank department values belong to the previous department, applying Fill Down produces the following result.

Department Employee Status
Sales Samiksha Active
Sales Omkar Active
Sales Pradnya Inactive
Support Neha Active
Support Samadhan Active
Result: Department labels are now available for every employee row, making grouping and filtering easier. This result is valid only if the source structure confirms the intended department for each row.

7. Practical Exercise

Exercise: Employee Dataset

Import a table containing Department, EmployeeName, City, JoiningDate, and Status.

  1. Inspect City values for inconsistent spelling and capitalization.
  2. Replace known abbreviations using an approved mapping.
  3. Inspect Department for null values.
  4. Use Fill Down only if the source layout confirms repeated department labels.
  5. Check JoiningDate for invalid values.
  6. Investigate errors before replacing them.
  7. Compare the cleaned result with the original data.
  8. Document the rules applied to each column.

Expected outcome: Consistent text values, correctly filled group labels, and documented error handling.

8. Knowledge Check

Question 1: What does Replace Values do?

A. Creates a new report page

B. Substitutes a specified value with another value

C. Publishes a dataset

D. Creates a relationship automatically


Question 2: What does Fill Down do?

A. Copies the next value upward

B. Deletes all null rows

C. Copies the previous non-null value downward into null cells

D. Converts every column to text


Question 3: When should you use Fill Up or Fill Down?

A. Whenever a column contains any null value

B. When the source structure justifies carrying a value between rows

C. Only in numeric columns

D. Before inspecting the source data


Question 4: What should you do before replacing an error?

A. Investigate its cause and determine a valid replacement

B. Replace it with zero every time

C. Delete the entire dataset

D. Ignore the error details

View Answers
  1. B — Substitutes a specified value with another value.
  2. C — Copies the previous non-null value downward.
  3. B — Use it when the source structure justifies the operation.
  4. A — Investigate the cause and determine a valid replacement.

Summary

In this lesson, you learned how to replace inconsistent values, standardize text entries, and use Fill Down and Fill Up to populate missing values when the source structure supports it.

You also explored how to handle errors and why replacements should follow documented business rules. Always inspect the source data and validate the final result before loading it into Power BI.