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.
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
- Open Power BI Desktop.
- Select Home > Transform data.
- Select the required query.
- Select the column you want to modify.
- Choose Replace Values from the available transformation options or column context menu.
- Enter the value to find and the replacement value.
- 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. |
Visual 2: Example of replacing inconsistent values.
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.
Visual 3: Fill Down carries the previous non-null value into null rows.
How to Apply Fill Down
- Select the column containing the null values.
- Open the Transform tab.
- Choose Fill > Down.
- Inspect the populated cells.
- Confirm that the filled values match the source structure.
How to Apply Fill Up
- Select the required column.
- Open the Transform tab.
- Choose Fill > Up.
- Inspect the resulting values and verify their meaning.
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. |
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.
- Select the column containing errors.
- Inspect the error details to understand the cause.
- Choose Replace Errors if a replacement is justified.
- Enter the approved replacement value.
- Verify the resulting values and data type.
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 |
7. Practical Exercise
Exercise: Employee Dataset
Import a table containing Department, EmployeeName, City, JoiningDate, and Status.
- Inspect City values for inconsistent spelling and capitalization.
- Replace known abbreviations using an approved mapping.
- Inspect Department for null values.
- Use Fill Down only if the source layout confirms repeated department labels.
- Check JoiningDate for invalid values.
- Investigate errors before replacing them.
- Compare the cleaned result with the original data.
- 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
- B — Substitutes a specified value with another value.
- C — Copies the previous non-null value downward.
- B — Use it when the source structure justifies the operation.
- 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.