7. Data Cleaning A–Z in Power Query
7.5 Blanks and Nulls: Remove, Replace, Fill Down / Fill Up
Start with a problem
In the Pune export, BLK-404 has no Order Date because the system writes the date only once per day. Shraddha also wants to replace the blank delivery time of a cancelled order with 0.
Explain simply
Fill Down copies the value above into null cells. Replace Values turns blanks into null (or another value). Do not replace a meaningful blank with 0 – it changes averages.
Show the data
Before (after 7.3):
| Order ID | Order Date |
|---|---|
| BLK-401 | 06-04-2026 |
| BLK-402 | 06-04-2026 |
| BLK-403 | 07-04-2026 |
| BLK-403 | 07-04-2026 |
| BLK-404 |
About this data: This table uses columns: Order ID, Order Date. This table uses columns: Order ID, Delivery Mins, Status. This table uses columns: Choice for 308, Average delivery (all 8 orders file). This table uses columns: City, Store, Amount (₹). Values shown are the lesson examples already on this page (not a new dataset).
Second example – delivery minutes (visuals_orders file):
| Order ID | Delivery Mins | Status |
|---|---|---|
| 305 | 7 | Delivered |
| 308 | null | Cancelled |
Fictional sample data. Practice file: pq_raw_pune_export.csv, visuals_orders_apr_may_2026.csv (source: src/practice/).
Demonstrate
-
Select Order Date › Transform › Replace Values › Value To Find: leave empty › Replace With:
null› OK.Your result should look like this:
BLK-404 Order Date null -
Transform › Fill › Down.
Your result should look like this:
BLK-404 Order Date 07-04-2026 -
For Delivery Mins of order 308 leave null. Compare averages in a card: average of 7 delivered values vs with 0 for 308.
Show the result
Your result should look like this (fictional sample):
| Choice for 308 | Average delivery (all 8 orders file) |
|---|---|
| Leave null (correct) | 77 ÷ 7 = 11.0 min |
| Replace with 0 (wrong) | 77 ÷ 8 = 9.6 min |
Explain what happened
CSV blanks arrive as empty text, which Fill Down ignores; converting them to null lets Fill Down work. Averages ignore nulls but count zeros.
Explore further
- Fill Up works the same way upwards. Remove Empty (filter) drops rows where the key is null.
Detailed reference for this lesson:
Fill down in Power Query with example — in 30 seconds
What: Fill Down copies the value above into the empty (null) cells below it.
Why: Excel reports often write the city once and leave the next rows empty. Without Fill Down those rows have no city.
Tiny example: Pune is written once for three rows. After Fill Down, all three rows say Pune.
Fill down in Power Query with example — 30 सेकंदात
काय: Fill Down वरची value खालच्या रिकाम्या (null) cells मध्ये copy करतो.
का: Excel reports मध्ये बऱ्याचदा शहर एकदाच लिहून पुढच्या rows रिकाम्या सोडतात. Fill Down शिवाय त्या rows ना शहरच नसतं.
छोटं उदाहरण: Pune तीन rows साठी एकदाच लिहिलंय. Fill Down नंतर तिन्ही rows मध्ये Pune दिसतं.
Fill down in Power Query with example — 30 सेकंड में
क्या: Fill Down ऊपर की value को नीचे की ख़ाली (null) cells में copy करता है.
क्यों: Excel reports में अक्सर शहर एक बार लिखकर अगली rows ख़ाली छोड़ देते हैं. Fill Down के बिना उन rows का कोई शहर नहीं होता.
छोटा उदाहरण: Pune तीन rows के लिए एक बार लिखा है. Fill Down के बाद तीनों rows में Pune आ जाता है.
First look at the data (before, then after):
Same before / after tables as text
Before — Monthly report from a store manager
| City | Store | Amount (₹) |
|---|---|---|
| Pune | Kothrud | 500 |
| null | Baner | 200 |
| null | Hadapsar | 300 |
| Mumbai | Andheri East | 400 |
| null | Powai | 100 |
Formula / click path
Select City › Transform › Fill › Down
After — After Fill Down (Pune ₹1,000 · Mumbai ₹500)
| City | Store | Amount (₹) |
|---|---|---|
| Pune | Kothrud | 500 |
| Pune | Baner | 200 |
| Pune | Hadapsar | 300 |
| Mumbai | Andheri East | 400 |
| Mumbai | Powai | 100 |
Do it step by step:
- Check the empty cells really show null. If they are blank text, use Replace Values (blank, then null) first.
- Select the City column.
- Click Transform › Fill › Down.
- Check the last rows. Mumbai must not spill into a new city.
एक-एक step करून बघा:
- रिकाम्या cells मध्ये खरंच null दिसतं का ते तपासा. Blank text असेल तर आधी Replace Values (blank, then null) वापरा.
- City column निवडा.
- Transform › Fill › Down वर click करा.
- शेवटच्या rows तपासा. Mumbai नवीन शहरात सांडता कामा नये.
एक-एक step करके देखिए:
- ख़ाली cells में सच में null दिखता है या नहीं, जाँचिए. Blank text हो तो पहले Replace Values (blank, then null) इस्तेमाल कीजिए.
- City column चुनिए.
- Transform › Fill › Down पर click कीजिए.
- आख़िरी rows जाँचिए. Mumbai किसी नए शहर में नहीं फैलना चाहिए.
Look: 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})))
Got it? Blank does not always mean zero. If not, read the example once more.
समजलं का? Blank म्हणजे नेहमी zero नाही. नसेल समजलं तर उदाहरण पुन्हा एकदा वाचा.
समझ आया? Blank का मतलब हमेशा zero नहीं होता. नहीं समझा तो उदाहरण एक बार फिर पढ़िए.
Ravindra Bagale's Tip
Friends, remember: 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" (for example 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. Keep this in mind!
Ravindra Bagale's Tip – मराठी
मित्रांनो, लक्षात ठेवा: रिकाम्या Delivery Time ला 0 ने replace केलं तर average delivery time खऱ्यापेक्षा चांगला दिसतो. Numeric blanks चा अर्थ "लागू नाही" असेल (उदाहरणार्थ cancelled order) तर त्यांना null राहू द्या, आणि 0 हीच खरी value असेल (discount, delivery fee) तेव्हाच 0 ने replace करा. Fill Down फक्त grouped exports साठी वापरा, जिथे blanks खरंच missing values आहेत तिथे कधीच नाही. लक्षात ठेवा!
Ravindra Bagale's Tip – हिंदी
दोस्तों, याद रखो: खाली Delivery Time को 0 से replace करने पर average delivery time असल से बेहतर दिखता है. Numeric blanks का मतलब "लागू नहीं" हो (जैसे cancelled order) तो उन्हें null रहने दो, और 0 से तभी replace करो जब 0 ही असली value हो (discount, delivery fee). Fill Down सिर्फ़ grouped exports के लिए इस्तेमाल करो, जहाँ blanks सच में missing values हैं वहाँ कभी नहीं. ध्यान रखना!
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".
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Fill Down does nothing | Cells are empty text, not null | Replace empty with null first |
| Average delivery looks better than reality | Null replaced with 0 | Keep null; filter Delivered in measures |
| Wrong date filled | Sorted before Fill Down | Fill Down in source order, then sort |
Practise independently
Practice task
After Fill Down, how many orders are dated 07-Apr-2026 (before de-duplication)?
Hints
- BLK-403 twice + 404.
Solution
3 rows (BLK-403, BLK-403, BLK-404); after de-duplication 2 orders, ₹770.
Think like a professional
Treat blanks as information: ask why a value is missing before choosing fill, replace or keep.
Revision card
Interview card