Resolving Nulls, Unexpected Values, and Inconsistencies
A sales table arrives with three records that deserve attention. One order has no discount amount. Another has a quantity of negative five. A third describes its status as Shipped, while other records use shipped, SHIPPED, and Complete.
You could replace the blank with zero, remove the negative quantity, and choose one status label. The interface would let you make those changes. But what evidence would show that you’d cleaned the data correctly rather than applied three convenient guesses?
That distinction matters for the PL-300 course blueprint. The blueprint includes resolving inconsistencies, unexpected or null values, and data quality issues. The important verb is resolving. Finding an unusual value isn’t enough. You need to determine what it means, choose an appropriate response, and confirm that the resulting data still represents the business accurately.
Start with the requirement, not a familiar cleaning command. Suppose the sales team says that a blank discount means no discount was offered. In that specific case, replacing the blank with zero could preserve the intended meaning. Now suppose the discount is blank because an older sales system didn’t capture discounts.
Zero would make a different claim. It would say you know there was no discount, when the truthful answer is that the value is unknown. The cells look identical, but their business meanings are not.
The same reasoning applies before deleting a record. A negative quantity might be malformed data, but it could also represent a business event that the dataset is expected to preserve. The supplied requirement has to tell you which interpretation is valid.
Removing the row merely because the value looks unusual could remove a legitimate event. Keeping every negative value without investigation could preserve an error. The number alone can’t settle the decision.
This is why a null, an unexpected value, and an inconsistency shouldn’t be treated as synonyms. A legitimate blank can be an intentional absence. A missing value can indicate that required information wasn’t captured.
A malformed value doesn’t conform to the meaning expected for that field. An inconsistency can occur when equivalent records use different representations, such as variations in a status label. Cleaning begins by separating those cases rather than sending all of them through one universal rule.
For this sales data, the analyst first writes down the expected outcome. Discount blanks that mean no discount may become zero. Blanks whose meaning is unknown must remain distinguishable from zero. Status values that the business confirms are equivalent may be standardized.
Suspicious quantities require a rule grounded in the business process. That statement gives each transformation a reason. It also makes the result reviewable by someone who wasn’t present when the query was created.
Power Query is Power BI’s data acquisition and transformation environment, so it’s the natural owning layer when the requirement is to clean records as data is prepared. Explicit replacement, filtering, or correction steps remain visible in the query history. That visibility matters because the transformation isn’t just an outcome. It’s a repeatable decision applied to the query output.
Compare that with hiding a blank in a report visual. The display may appear cleaner, but the underlying query output hasn’t been corrected. Another visual or calculation could still encounter the original value. A report-layer choice can be appropriate when the requirement is specifically about presentation. It isn’t a substitute for query-time cleaning when the requirement says the prepared records themselves must be standardized or corrected.
A semantic-model calculation can also produce a desirable displayed result without changing the prepared column. For example, a calculation might present a preferred outcome when it encounters a blank. That affects the calculation’s result in its evaluation context. It doesn’t establish that the source record was corrected in Power Query. The two approaches answer different requirements, even if one report page makes them look similar.
The Power BI service is another distinct context. Refresh and access state can affect what consumers receive, but service configuration doesn’t decide whether a missing discount means zero. That meaning belongs to the data requirement. Choosing the controlling layer keeps you from troubleshooting a report interaction, service permission, or model expression when the actual cause is a query transformation.
Return to the status field. Assume the business owner confirms that Shipped, shipped, and SHIPPED all mean the same state, while Complete means something different. The analyst can correct the confirmed variants to one agreed representation and leave Complete alone. Replacing every unusual label with Shipped would go beyond the evidence. Standardization is useful only when the supposedly equivalent values really are equivalent.
Now consider the negative quantity. The report’s normal view happens to exclude it because that page is filtered to completed sales. The totals look reasonable, so someone declares the cleaning successful.
That conclusion is premature. The suspicious record still exists, and the normal filter merely keeps it out of view. To investigate correctly, reproduce the condition that exposes the record and compare the expected result with the actual query output.
Suppose the requirement confirms that this dataset should contain sales only and that negative quantities came from an import defect. The correction belongs in Power Query because the prepared sales records need to satisfy that rule. The analyst might correct the value if an authoritative value is available, filter the record if the requirement explicitly says invalid records must be excluded, or preserve it for further resolution if neither action is justified. The interface offers all three possibilities. The requirement determines which one is defensible.
That’s the decision rule worth retaining. Don’t replace a null with zero and don’t delete a record until you understand the missing or unexpected value’s meaning. Zero, null, correction, and removal aren’t cosmetically different versions of the same answer. They describe different facts about the business. A successful transformation can still be wrong if it turns uncertainty into certainty or discards a record that should remain visible.
An explicit query history helps expose those decisions. Imagine seeing a step that replaces every blank discount with zero. You can ask whether all blanks share the same meaning. If they don’t, the step is too broad.
You might need a condition that distinguishes records from systems where blank means no discount from records where the discount wasn’t captured. The exact business rule comes first. The transformation expresses that rule rather than inventing it.
Ordering also deserves attention because you’re evaluating the output of a sequence of steps. Suppose status labels are corrected and then records with an unapproved status are filtered. You need to inspect whether the correction produces the intended values before the filter evaluates them. If a later step removes a record unexpectedly, examine the earlier steps that shaped the value it received. Don’t assume the visible symptom was caused by the last command you remember adding.
Apply that discipline across the sales query. Begin with a profile of the incoming columns. Investigate which fields contain nulls, which values are rare or unexpected, and which representations may be inconsistent. Then classify the findings according to business meaning.
A null discount can be legitimate, missing, or unresolved. A quantity can be expected or suspicious under the stated rule. A status variant can be equivalent to an approved label or represent a genuinely different state.
Only after that classification should you select replacement, filtering, or correction. Replacement is suitable when the substitute is justified. Filtering is suitable when the requirement authorizes exclusion of those records. Correction is suitable when you can identify the intended value. If the meaning remains unknown, preserving the value for further investigation can be more accurate than forcing the table to look complete.
Each decision has downstream consequences. Replacing unknown discounts with zero can make a report behave as though those orders definitely received no discount. Removing suspicious quantities can change the set of sales records available to the semantic model. Combining genuinely different status values can erase a distinction report consumers need. Cleaning improves quality only when it preserves the meaning required downstream.
The nearest alternative may produce an attractive report without solving the data problem. A visual can omit blank categories. A calculation can return a preferred display value.
A filter can hide records from one page. None of those observations, by itself, proves that the Power Query output is clean. If the business requirement concerns prepared data, inspect prepared data.
If it concerns a calculation, inspect that calculation. If it concerns a report interaction, test the interaction. Correctness has to be validated in the layer where the behavior occurs.
For the discount field, inspect the query output after the relevant step. Records known to represent no discount should contain the approved result. Records whose discount is unknown should not have been silently converted to the same result.
For status, inspect the distinct representations that remain after correction. The confirmed spelling variants should be standardized, while Complete should remain distinct under this hypothetical rule. For quantity, confirm that any corrected or excluded records match the documented requirement.
Power Query’s profiling tools provide useful evidence here. Column quality, Column distribution, and Column profile help you investigate the condition of the data. They can reveal nulls, errors, frequencies, and the range of values represented in the profiled data.
That evidence supports investigation, but it doesn’t supply the business meaning on its own. A rare value may be wrong, or it may be a valid exception. Profiling tells you where to ask a better question.
There’s an important boundary to that evidence. In Power BI Desktop, profiling uses the first one thousand rows by default unless you select profiling for the entire dataset. Imagine that every discount in those first one thousand rows is populated, while later records contain nulls. The initial profile can look clean even though the broader dataset isn’t. Before treating the profile as proof, check which population it describes.
This becomes especially important when troubleshooting a refresh. Suppose the original sample contained only the status values Shipped and Complete. A later refresh brings in SHIPPED and an empty status from another source system. The report still opens, and the refresh itself may complete, but that doesn’t prove the cleaning rule handled the new data correctly. Reproduce the changed condition and inspect the refreshed query output.
First compare expected and actual behavior. Did SHIPPED become the approved Shipped label? Did the empty status remain unresolved, receive a justified replacement, or get removed without authorization?
Then inspect the query step that directly controls that field. Avoid changing an unrelated visual simply because that’s where the symptom became noticeable. After correcting the responsible step, retest both the original values and the new edge cases.
That second test protects against a narrow fix. A replacement aimed at SHIPPED shouldn’t accidentally convert Complete. A condition for empty status values shouldn’t affect legitimate blanks in an unrelated column. A quantity correction shouldn’t change valid positive quantities. Verification should show that the failing case now meets the requirement and that the normal case still behaves as intended.
Profile the corrected output again, with the profiling population understood. Examine the values that remain and compare them with the rule you meant to implement. Then follow the result into the semantic model and the report where appropriate.
If a report total was affected by excluded records, check that result. If a category label was standardized, confirm that consumers now receive the intended grouping. The query output is the first proof for a query transformation, while downstream behavior shows whether the prepared data supports the solution as expected.
This also exposes a common troubleshooting mistake. An analyst sees a report problem and reaches for the closest available feature. A visual filter hides an inconsistent category, so the page looks fixed.
But another report page still shows it because the inconsistency remained in the prepared data. Restating the requirement would have made the correct layer clearer. If every downstream use should receive standardized status values, correct them during data preparation rather than hiding them one visual at a time.
The opposite mistake is possible too. If the requirement is only to present a particular report interaction, changing source-facing query values may be unnecessary and could alter other uses of the data. The goal isn’t to force every issue into Power Query. It’s to identify what must change, locate the layer that owns that behavior, and validate the outcome there. For this episode’s cleaning requirement, Power Query owns the explicit preparation steps, while the model, calculations, reports, and service provide separate places where downstream effects may need to be checked.
By the end of the investigation, the analyst should be able to explain every material action. This blank became zero because the business defined it as no discount. That blank remained unresolved because its value wasn’t captured.
These spelling variants were standardized because they represent the same approved status. This suspicious quantity was handled according to an explicit sales-data rule. Each statement connects the query step to a business meaning and to observable evidence.
That standard is stronger than saying the transformation ran successfully. It helps during refreshes, when new values can appear. It helps during troubleshooting, when a report symptom may originate in the query.
And it helps prevent tidy-looking data from making claims the source and requirement don’t support. Clean data isn’t simply data with fewer blanks or fewer distinct values. It’s data whose treatment can be explained, inspected, and defended.
Cleaning nulls, unexpected values, and inconsistent records is a reasoning task before it’s a transformation task. First determine whether a blank is legitimate, missing, or malformed, and whether an unusual value is an error or a meaningful exception. Then choose an explicit replacement, filter, or correction that preserves that meaning in Power Query.
Finally, validate the query output and any affected downstream result instead of trusting a successful refresh or a tidy report page. When you can connect each step to a business rule and show testable evidence that normal and edge cases behave correctly, you’ve done more than make the data look consistent. You’ve produced a cleaning decision that another analyst can inspect, understand, and defend.
Separate a true null, an unexpected value, and an inconsistent representation of the same value. Which correction would you apply, and what normal case would you retest afterward? The matching episode reference provides the technical inventory and a concise lab checklist for this lesson. This lesson was produced by Bare Metal Cyber dot com.