A Power Query fill down step can silently propagate values from one customer’s row into another’s if the rows are simply sorted together. For example, filling down in a global table might carry “North” from Customer A into a leading null row of Customer B. Our playbook addresses exactly this scenario: only an earlier non-null value in the same Customer partition (with approved sequence order) may donate. We preserve each row’s original RawRegion value and RowID so we can trace whether a filled value came from a valid donor. We distinguish a populated cell (non-null or explicit empty string) from a fill-down result, as they have different meanings.
This is a documented contract issue, not a new feature: Table.FillDown by design propagates previous values without regard for business partitions. To govern self-service BI semantics, we impose an additional rule: the donor’s Customer must match the row’s Customer and its sequence must be less than or equal to the target’s sequence.
Our evidence comes from a hand-authored nine-row fixture (all rows and amounts preserved) and independent expected “oracle” values. We have not run this in Power BI Desktop yet, so the outputs we show are expected from the documented behavior, not recorded results. This playbook will guide BI analysts and reviewers through accepting or rejecting each filled row based on clear lineage and partition rules, ensuring no value “leaks” across customers.
Define which customer may donate a missing value
We start by clarifying the contract. Under the default Table.FillDown, a null cell simply takes the last seen non-null value in that column. For example, if A1 (Cust=A, Seq=1) has “North” and A2 is null, then B1 (Cust=B, Seq=1) sorted below A2 will be filled with “North” too. But our business rule forbids that: only rows of the same Customer can share values. Thus, B1 should remain null (no prior B-value exists), not inherit A’s value. We therefore define: a donor must come from the same Customer and have a sequence ≤ the target’s. We keep the original RawRegion untouched in a separate column; a filled value is just a justified copy of an earlier value, not a new unknown.
We also note: an empty string is considered a deliberate value (not equivalent to null) under our baseline policy. The Power Query guidance shows you should explicitly convert blanks to null if you want them treated as missing. We will keep “<empty>” (empty text) distinct from <null> in displays: e.g. C1’s RawRegion is "" (we’ll show it as <empty>) meaning the region is intentionally empty. It isn’t an instruction to erase a previous value. In contrast, a raw <null> is an absence that can be filled. Leading and all-null rows (like a customer with only nulls) have no eligible donor and are left unfilled (<null> remains, marked NO_PRIOR_VALUE).
(For a detailed example of preserving raw meaning and contracts in data conversion, see how we preserved raw text in a date-validation lab.)
Create a local blank-query environment
Use a fresh local Power BI Desktop blank-query environment. Record the exact Desktop and operating-system builds, locale and query revision. The illustrative setup uses Windows 11, an en-US locale and query revision DC-2026-10-01-r1. All data is in one #table literal. We define the table schema explicitly:
let Source = #table( type table [ RowID = text, Customer = text, Seq = Int64.Type, RawRegion = nullable text, Amount = Int64.Type ], { {"A1", "A", 1, "North", 10}, {"A2", "A", 2, null, 10}, {"B1", "B", 1, null, 10}, {"B2", "B", 2, "South", 10}, {"B3", "B", 3, null, 10}, {"C1", "C", 1, "", 10}, {"C2", "C", 2, null, 10}, {"D1", "D", 1, null, 10}, {"D2", "D", 2, null, 10} } )inSource
We ensure no type inference beyond this: each column has the intended type. We will keep two orders in mind: the input order (as above) and the approved sequence order (Customer A then B then C then D, each sorted by Seq).
The RawRegion column contains string values "North"/"South", literal null, and an explicit empty text for C1. We will display empty as <empty> and null as <null> for clarity (a display convention only, not a data change). The Amount column is uniformly 10 so totals (90) remain unchanged through transformations, which by themselves cannot catch cross-customer filling.
Make null and empty string visibly different
For our tables and debug output, we will print raw nulls as <null> and raw empty text as <empty>. (This is a visual aid; the stored values remain unchanged.) For example, the initial table shows RawRegion: "North", <null>, <empty>. Under our baseline policy, the empty cell in C1 counts as a non-null value (intentional blank), not a missing marker. We do not convert it to null unless testing an alternative policy.
Write the independent nine-row donor oracle
We hand-author the expected output to validate our implementation. The input fixture rows (RowID, Customer, Seq, RawRegion) are:
1. A1 = (A,1,North)
2. A2 = (A,2,null)
3. B1 = (B,1,null)
4. B2 = (B,2,South)
5. B3 = (B,3,null)
6. C1 = (C,1, empty string)
7. C2 = (C,2,null)
8. D1 = (D,1,null)
9. D2 = (D,2,null)
Each has Amount = 10. Based on our same-customer rule, the expected filled values and donors are:
10. A1: value=North, donor=A1 (self).
11. A2: value=North (carried from A1), donor=A1.
12. B1: value=<null> (no prior B), donor=<null> (no donor).
13. B2: value=South (self), donor=B2.
14. B3: value=South (carried from B2), donor=B2.
15. C1: value=<empty> (self), donor=C1.
16. C2: value=<empty> (carried from C1), donor=C1.
17. D1: value=<null> (no prior D), donor=<null>.
18. D2: value=<null> (no prior D), donor=<null>.
All nine RowIDs remain in output. Note that empty string stays as a value for C1/C2. We put these expected outcomes into a separate “Expected” query or table rather than computing them with fill-down; this is our oracle. The core row count is 9 and total Amount is 90, but as we will see, matching those aggregates does not prove the fill logic is correct.
Keep expected donors separate from the implementation
We implement the expected table by hand. For example, one can write:
let Expected = #table( type table [RowID=text, ExpectedRegion=nullable text, ExpectedDonorRowID=text], { {"A1", "North", "A1"}, {"A2", "North", "A1"}, {"B1", null, null}, {"B2", "South", "B2"}, {"B3", "South", "B2"}, {"C1", "", "C1"}, {"C2", "", "C1"}, {"D1", null, null}, {"D2", null, null} } )inExpected
This table (with "", <empty>, and <null> as shown) defines the oracle. We keep it independent of the actual filling logic: if our fill code matches this table, we have satisfied the contract. We do not let the fill procedure generate its own expected values; we encode them explicitly. This way we ensure nothing is inadvertently assumed.
Run the global fill that crosses the boundary
First, we simulate the naïve approach: sort all data by Customer then Seq, create a combined “carry” record column, and apply Table.FillDown on that record across the entire table. In M, one might do:
let SortedGlobal = Table.Sort(Source, {"Customer", Order.Ascending}, {"Seq", Order.Ascending}), AddCarry = Table.AddColumn(SortedGlobal, "Carry", each if [RawRegion] <> null then [Value=[RawRegion], DonorRowID=[RowID], DonorCust=[Customer], DonorSeq=[Seq]] else null, type record ), FilledGlobal = Table.FillDown(AddCarry, {"Carry"}), Expanded = Table.ExpandRecordColumn(FilledGlobal, "Carry", {"Value","DonorRowID", "DonorCust","DonorSeq"}, {"FilledRegion","DonorRowID","DonorCustomer","DonorSeq"})inExpanded
Because we sorted by Customer then Seq, the SortedGlobal has rows A1,A2,B1,B2,B3,C1,C2,D1,D2 in that order. The FillDown will propagate values from one row to the next regardless of customer. The resulting table (displaying only key columns) is:
RowID | Customer | Seq | RawRegion | FilledRegion | DonorRowID |
A1 | A | 1 | North | North | A1 |
A2 | A | 2 | <null> | North | A1 |
B1 | B | 1 | <null> | North | A1 |
B2 | B | 2 | South | South | B2 |
B3 | B | 3 | <null> | South | B2 |
C1 | C | 1 | <empty> | <empty> | C1 |
C2 | C | 2 | <null> | <empty> | C1 |
D1 | D | 1 | <null> | <empty> | C1 |
D2 | D | 2 | <null> | <empty> | C1 |
Here we see the defect: B1 wrongly received “North” from A1 (DonorRowID=A1), and D1/D2 incorrectly received the empty value from C1 (DonorRowID=C1). These were global column-level fills, not respecting Customer boundaries. We do not call this a bug in Table.FillDown; it did exactly what it’s supposed to do (propagate the prior record’s value). The problem is our business contract. We label this output “GlobalFill” and keep it for comparison, but we won’t accept these cross-customer values.
Notice the totals: Amount sum is still 90 (9 rows × 10). Reducing the count of nulls or the sums would not by themselves show the problem. Only the row-by-row donor check will.
Show why a customer sort is not a customer reset
Sorting the table by Customer and Seq does not enforce any reset of carried values at customer boundaries; the carry simply continues through the sorted list.
For instance, if the input rows came in a mixed order (say A1, B2, A2, B1, B3, C1, C2, D1, D2), after sorting the same global sequence would result. Even if the Customer blocks happened to coincide with the sorted order, the fill logic doesn’t “know” when one Customer ends. In other words, merely ensuring all of B’s rows appear after A’s in the list is not sufficient; a null in B still gets A’s last value. The mistake is relying on adjacency in display order rather than grouping logic.
To illustrate independence from input order, we can shuffle the same 9 rows, sort by Customer/Seq, and get the same GlobalFill result. This shows the carry state is preserved across the artificial boundary. (Table.Sort itself does not guarantee stability on identical keys, but here all (Customer,Seq) pairs are unique.) The key lesson is: sorting alone is no substitute for resetting carry per group. We need an explicit grouping strategy (as in the next section) to properly restart the fill inside each customer.
Validate partition keys and sequence before filling
Before we apply any fill logic, we must check the input meets our partition contract: each RowID should be unique, and Customer and Seq must be non-null for rows that should participate. Moreover, each Customer must have unique Seq values. Any violation triggers a HOLD. For example:
19. Null Customer or Seq: If a row has Customer = <null> or Seq = <null>, we cannot assign it to a group or order it; this row’s handling must be decided by policy or upstream owner, not by fill.
20. Duplicate Seq within Customer: If two rows for Customer “G” both have Seq=1 (contradictory values), we do not pick one arbitrarily. Instead we flag an error (HOLD) because the approved ordering is ambiguous.
In practice, one could implement checks in M such as:
// Example validation logic (conceptual)
if Table.RowCount(Table.SelectRows(Source, each [Customer] = null or [Seq] = null)) > 0 then
error "Partition key missing (Customer or Seq)";
if Table.RowCount(Table.Distinct(Table.TransformColumnTypes(Source, {{"Customer",
type text},{"Seq", type text}}))) < Table.RowCount(Source) then
error "Duplicate (Customer,Seq) pair detected";
Each of these conditions would cause the query to halt with a clear message. We keep these malformed cases separate from our core 9-row fixture (for example, additional queries or branches). Under our stated contract, a duplicated sequence is a “HOLD (unsupported)” scenario, not an opportunity to just pick the first one. Similarly, unknown or null keys are held until resolved by data policy.
Fill inside a sorted Table.Group transformation
We now implement the proper partitioned fill. We define a reusable function FillGroup that takes all rows of one Customer, sorts them by Seq ascending, adds a “Carry” column containing a record of value+provenance, fills that, and extracts the fields. Then we apply this function to each customer. For example:
let FillGroup = (custTable as table) => let // Sort rows within customer by Seq SortedCust = Table.Sort(custTable, {"Seq", Order.Ascending}), // Optionally buffer to fix row order before fill Buffered = Table.Buffer(SortedCust), // Add a Carry record for each row WithCarry = Table.AddColumn(Buffered, "Carry", each if [RawRegion] <> null then [Value=[RawRegion], DonorRowID=[RowID], DonorCustomer=[Customer], DonorSeq=[Seq]] else null, type record ), // Perform fill-down on the Carry column only CarryFilled = Table.FillDown(WithCarry, {"Carry"}), // Expand the Carry record into separate fields Expanded = Table.ExpandRecordColumn(CarryFilled, "Carry", {"Value","DonorRowID", "DonorCustomer","DonorSeq"}, {"FilledRegion","DonorRowID","DonorCustomer","DonorSeq"}) in Expanded, // Group by Customer and apply FillGroup to each partition Groups = Table.Group(Source, {"Customer"}, {"Transformed", each FillGroup(_), type table} ), // Combine groups back into one table Combined = Table.Combine(Groups[Transformed]), // Finally sort by Customer/Seq for consistent output presentation SortedAll = Table.Sort(Combined, {"Customer", Order.Ascending}, {"Seq", Order.Ascending})inSortedAll
Key points: we did not use GroupKind.Local; we use the default grouping (which simply invokes our function on each distinct Customer). Inside FillGroup, we sort by Seq and even buffer to freeze order before filling.
We add one compound record column “Carry” combining value and donor info, then do Table.FillDown on that single column. This ensures the carried value and its donor travel together. After filling, we expand the record into FilledRegion, DonorRowID, DonorCustomer, DonorSeq. The rest of the original columns (RowID, RawRegion, Amount, etc.) are preserved unchanged.
Carry value and provenance as one record
Notice in FillGroup we create one record per row to hold both the region value and provenance. For a row with a non-null RawRegion, we set:
Carry = [
Value = [RawRegion],
DonorRowID = [RowID],
DonorCustomer = [Customer],
DonorSeq = [Seq]
]
If RawRegion is null, we set Carry=null. Then Table.FillDown propagates this single record downward. This is crucial: if we instead filled values and donor columns separately, we risk misalignment or leaving holes. By carrying them together, we ensure each filled region comes with its matching donor.
After fill, we expand the record: e.g. FilledRegion = if [Carry]=null then null else [Carry][Value], and similarly for the Donor fields. All original raw columns (RawRegion, RowID, Customer, Amount) stay intact, along with the new fields.
Prove donor eligibility for every prepared row
Now that we have the partitioned fill output (SortedAll), we verify it against our oracle.
For each non-null filled row, we check three things:
21. DonorCustomer must equal Customer.
22. DonorSeq ≤ Seq.
23. The RawRegion at the DonorRowID must indeed match the filled value.
We join the result to the Expected table on RowID to compare values. In M, one could do:
let // Join actual and expected by RowID Joined = Table.NestedJoin(SortedAll, "RowID", Expected, "RowID", "Exp", JoinKind.LeftOuter), Expanded = Table.ExpandTableColumn(Joined, "Exp", {"ExpectedRegion", "ExpectedDonorRowID"}, {"ExpectedRegion","ExpectedDonorRowID"}), // Flag mismatches Check = Table.AddColumn(Expanded, "IsMatch", each ([FilledRegion] = [ExpectedRegion] or ([FilledRegion] = null and [ExpectedRegion] = null)) and ([DonorRowID] = [ExpectedDonorRowID] or ([DonorRowID] = null and [ExpectedDonorRowID] = null)) )inCheck
In this joined table, each row lists: RowID, RawRegion, Customer, Seq, FilledRegion, DonorRowID, and the corresponding ExpectedRegion and ExpectedDonorRowID. We then compute an IsMatch flag (true only if both the filled value and donor match the expectation, treating nulls carefully). We verify: All nine rows should appear with IsMatch=true in a successful case. Also, the row count remains 9 and Amount sum 90, confirming no rows were lost or duplicated.
For example, after grouping sort fill we get (values shown as <empty>/<null> for clarity):
RowID | RawRegion | Cust | Seq | FilledRegion | DonorRowID | Expected | Value | Donor |
A1 | North | A | 1 | North | A1 | A1 | Yes | Yes |
A2 | <null> | A | 2 | North | A1 | A1 | Yes | Yes |
B1 | <null> | B | 1 | <null> | <null> | <null> | Yes | Yes |
B2 | South | B | 2 | South | B2 | B2 | Yes | Yes |
B3 | <null> | B | 3 | South | B2 | B2 | Yes | Yes |
C1 | <empty> | C | 1 | <empty> | C1 | C1 | Yes | Yes |
C2 | <null> | C | 2 | <empty> | C1 | C1 | Yes | Yes |
D1 | <null> | D | 1 | <null> | <null> | <null> | Yes | Yes |
D2 | <null> | D | 2 | <null> | <null> | <null> | Yes | Yes |
All rows match the expected values and donors. In particular, the global-fill cases (B1 and D1/D2) that we reject would have mismatches if they had wrong donors; our function avoided that. We also confirm the total Amount = 90.
It’s worth noting why the naive global fill could have slipped by simple checks: the count of rows didn’t change, and the sum of a constant Amount also didn’t change. A reviewer looking only at totals or null counts might miss the cross-group fill. That’s why lineage checks are needed.
Make the query fail on a semantic mismatch
Finally, we embed assertions so that any deviation triggers an error. Save the preceding comparison as a query named Reconciled, then use its IsMatch flag in the assertion:
let // Identify any problem rows Errors = Table.SelectRows(Reconciled, each [IsMatch] = false or [Customer] = null or [Seq] = null), // If any, raise a record with details Result = if Table.RowCount(Errors) > 0 then error Error.Record("FillValidationError", "Donor or value mismatch detected.", Errors) else Reconciledin ResultIf any row fails (wrong donor or value, extra/missing row, or an invalid key), this assertion emits a record error containing the offending rows. We do not sweep errors under the rug. This way a query editor must see and fix the issue rather than hiding it.
Retest with input permutations and adverse controls
We ensure robustness by re-running the same process on variants. First, permute the valid 9-row input; after grouping and filling, the results must be identical (permutation-insensitivity). Any change in RowID mapping flags an issue.
Next, test negative controls separately:
24. New customer with only nulls: Add E1=(E,1,<null>). Its group has no values, so E1’s FilledRegion remains <null> and donor <null>. We keep it separate (and it would be expected to match an oracle saying “E1: no prior value”).
25. All-null group: Add F1=(F,1,<null>), F2=(F,2,<null>). Both remain null/donor null.
26. Duplicate sequence conflict: Add G1=(G,1,East) and G1b=(G,1,West). Here Seq=1 appears twice for G. Since the values differ, we must hold or error. Our validation logic should catch this and error out; we should not produce a single winner.
27. Missing Customer: Add H1=(<null>,1,South). Lacks Customer key, so also triggers a HOLD/error.
28. Contradictory expected donor: If we intentionally set an “Expected” donor that doesn’t match what group fill would do, we should see our assertion fail.
These control cases are evaluated separately (e.g. in other queries) to demonstrate that our acceptance tests only succeed for valid inputs. Any control scenario should be rejected by our checks and marked for review.
Repair from raw inputs and hold unsupported order
When an unacceptable fill is found (like cross-customer leakage), the fix is to reprocess from the preserved raw data using the correct grouping logic. In practice, we discard the faulty FilledRegion column and any interim steps, and rebuild the output by rerunning the grouping function on the original RawRegion. For example, we would replace the global fill step in the query with our FillGroup approach above. The unchanged RawRegion values remain as the ultimate source-of-truth for original values; we never guess values for filled cells from that point.
If some rows still have Customer=null or duplicate Seq after this (because raw input was invalid), those rows must be held or sent back to the data owner; we cannot safely infer anything. In any case, the trustworthy outputs are those produced by our group-fill, joined with the original 9 core rows.
Separate query rollback from semantic recovery
It’s important not to simply revert to a prior query version if that version also did a global fill. Rolling back to a known-bad state just reintroduces the defect. The proper recovery is to pinpoint the raw data step (our Source) and rerun the transformation using the reviewed grouping logic.
We should document: Raw inputs used (the #table above), the query revision (e.g. “DC-2026-10-01-r1, after fix”), and the Expected table for auditing. Downstream reports or data artifacts that depended on the incorrect fill should be flagged for review. Essentially, we reprocess from the authority (raw table) rather than trusting an intermediate filled table.
Choose accept, repair, reprocess or hold with clear ownership
Below is a decision matrix for each case, with assigned responsibility:
Condition | Disposition / Action | Owner / Notes |
Valid raw value (non-null RawRegion) | Accept (no change) | Data owner defined this; nothing to fill. |
Valid carried value (same cust, seq ≤ current) | Accept (record provenance) | BI developer implements fill; Reviewer verifies donor. |
Explicit unresolved null (no prior in cust) | Accept (unfilled) | Policy owner designated <null> as final. |
Value filled from another customer (cross-group) | Repair (reprocess with group-fill) | BI developer fixes transformation; data owner revalidates. |
Invalid/missing Customer or Seq | Hold (policy review) | Data product owner must resolve key issue. |
Duplicate Seq in a Customer | Hold/Error (no guess) | Data owner to correct ordering. |
Filled value lost provenance or mismatch | Error (assert fail) | Reviewer / BI dev must investigate. |
29. Source Owner (Data Policy): sets the partition rules (which Seq is approved, how to treat null vs empty). They approve contract changes (e.g. if empty string should mean unknown).
30. BI Developer: writes and updates the query; ensures grouping/sorting logic is correct.
31. Reviewer: examines donor lineage, runs the assertion checks, signs off on each filled row or calls for fixes.
32. Report Owner: receives the final dataset; if a fill error was repaired or held, they may need to adjust reports or note missing data.
After any code or policy change, we re-run the grouping logic and assertion test. Any change in schema, grouping key, or definition of null/empty may require revisiting the expected values.
Build the BI foundations behind trustworthy preparation
Defensive checks like these fit into a broader BI data governance approach. For example, building semantic models and data pipelines with clear business rules (as emphasized in guided BI programs) ensures that transformations align with meaning.
Refonte Learning’s Business Intelligence Essentials program is a 3-month beginner track (roughly 8–10 hours/week) that trains analysts in data modeling, SQL, Excel and BI tools like Power BI. It requires enrollees to be working toward a bachelor’s or higher degree. On completion students earn a Training Certificate and Certificate of Internship (contingent on performance, not a guaranteed job).
While we won’t claim this specific course teaches our niche scenario, it does cover data analysis and report-building foundations that underpin why we validate things like fill-down.
In summary: Accept a filled value only if its row-by-row donor matches the same Customer partition (Seq ≤ current) and the donor’s original value was explicit. Otherwise, hold or repair by regrouping. We’ve now codified the “Partitioned Fill Contract” for Trustworthy Data: DonorCustomer = RowCustomer & DonorSeq ≤ Seq. By enforcing this and preserving RawRegion + RowID, we maintain auditability and catch errors that simple aggregates would miss.
Ready to review Refonte Learning’s Business Intelligence Essentials program for more on these topics (3 months, beginner level, student prerequisite).