7. Data Cleaning A–Z in Power Query
7.5 Blanks and Nulls: Remove, Replace, Fill Down / Fill Up
He bagha: in Power Query an empty cell usually shows as null. An empty text "" is not the same as null.
Before
| Category | Product | Discount | Partner ID |
|---|---|---|---|
| Dairy & Breakfast | Gokul Cow Milk 500 ml | null | DP-01 |
| null | Amul Butter 100 g | 5 | null |
| null | Poha 1 kg | null | DP-03 |
| Fruits & Vegetables | Nashik Grapes 500 g | 10 | DP-02 |
| null | Nagpur Oranges 1 kg | null | null |
After
| Category | Product | Discount | Partner ID |
|---|---|---|---|
| Dairy & Breakfast | Gokul Cow Milk 500 ml | 0 | DP-01 |
| Dairy & Breakfast | Amul Butter 100 g | 5 | Unknown |
| Dairy & Breakfast | Poha 1 kg | 0 | DP-03 |
| Fruits & Vegetables | Nashik Grapes 500 g | 10 | DP-02 |
| Fruits & Vegetables | Nagpur Oranges 1 kg | 0 | Unknown |
Choose the fix based on what the blank means:
| Situation | Best action | Command |
|---|---|---|
| Category typed once per group, blank below (grouped report layout) | Fill Down | Transform › Fill › Down |
| The value appears at the bottom of a group (subtotal layouts) | Fill Up | Transform › Fill › Up |
| Blank discount means "no discount" | Replace with 0 | Transform › Replace Values (Value To Find: null) |
| Blank partner on a cancelled order | Replace with "Unknown" | Transform › Replace Values |
| Row has no Order ID at all (junk row) | Remove | Home › Remove Rows › Remove Blank Rows, or filter out (null) |
Steps in Power BI
- Select Category › Transform › Fill › Down.
- Select Discount › Transform › Replace Values › Value To Find:
null› Replace With:0› OK. - Select Partner ID › Transform › Replace Values ›
null→Unknown. - To remove rows where every column is empty: Home › Remove Rows › Remove Blank Rows.
- To remove rows where one column is empty: click the column's filter arrow › untick (null) (or Remove Empty).
Filled = Table.FillDown(Source, {"Category"}),
Zeroed = Table.ReplaceValue(Filled, null, 0, Replacer.ReplaceValue, {"Discount"}),
Unknown = Table.ReplaceValue(Zeroed, null, "Unknown", Replacer.ReplaceValue, {"Partner ID"}),
NoEmptyRows = Table.SelectRows(Unknown, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
Samjla ka? Blank mhanje nehmi zero nahi. Nasel tar udaharan punha ekda vacha.
Ravindra Bagale's Tip
Mitrano, lakshat theva: replacing a blank Delivery Time with 0 makes the average delivery time look better than it really is. Leave numeric blanks as null when they mean "not applicable" (udaharan mhanje a cancelled order), and replace with 0 only when 0 is the true value (discount, delivery fee). Use Fill Down only for grouped exports, never where blanks are genuinely missing values. Dhyan rakho!
Practice task
In a Sambhaji Nagar (CIDCO) product list, Sub Category appears only on the first row of each group. Fill it down. Then replace blank MRP with null (not 0) and blank Brand with "Unbranded".