Diagnosing Data Quality Issues Before Modeling

This episode focuses on a practical PL-300 objective: Diagnosing Data Quality Issues Before Modeling. Data quality analysis should identify duplicates, missing required values, unexpected categories, and incorrect types before those issues become harder to diagnose downstream. T…

A sales report refreshes without displaying an error. Its totals look reasonable, its charts respond to filters, and nobody notices anything obviously broken. Is that enough evidence that the data meets the business requirement? Not necessarily.

The source could contain duplicate identifiers, missing required values, unexpected status labels, or numbers stored with the wrong data type. A report can appear functional while resting on assumptions that were never tested. The important question isn’t whether Power BI accepted the data. It’s whether the data satisfies clearly defined quality rules, and whether you can demonstrate that with testable evidence.

This distinction belongs directly within the current Power BI Data Analyst scope. The relevant skill is to resolve inconsistencies, unexpected or null values, and data quality issues. That wording doesn’t prescribe one universal cleaning operation. It asks you to recognize what counts as a problem in context, choose the layer that should handle it, and confirm that the result matches the requirement.

A null value might be unacceptable in one column and entirely legitimate in another. A repeated value could reveal a duplicate record, or it could be expected because the table contains several lines for each sales order. You need the business meaning before you can define the rule.

Suppose an analyst receives sales and operations data containing an order identifier, order date, region, order status, quantity, and sales amount. The business says every imported row must have an order identifier and order date. Status must belong to an approved set. Region may be blank for records that haven’t yet been assigned.

The identifier is expected to repeat when one order contains several product lines, so it isn’t unique at the row level. Those statements turn a vague request for clean data into testable rules. They describe completeness, expected domains, and uniqueness in relation to the table’s actual grain.

An expected domain is the set or range of values that the business considers valid. For order status, the domain might be the approved status labels supplied in the requirement. For quantity, the domain could be constrained by whatever values the business process permits. Completeness asks whether values required for a record’s purpose are present.

Uniqueness asks whether a value that’s supposed to identify one thing actually does so at the intended grain. Data-type checks ask whether a column is represented in a form appropriate to its meaning. Distribution checks then help reveal patterns such as an unexpectedly dominant category, a sudden rise in blanks, or values that sit far outside the pattern you expected.

These checks should happen before untested assumptions spread into the semantic model and its measures. If a model treats an identifier as unique when the table really contains order lines, relationships and calculations can be designed around the wrong grain. If two labels represent the same business status but remain inconsistent, a report may present them as separate categories.

If required values are missing, a total might still calculate while silently excluding or grouping records in an unintended way. The purpose of a quality rule is therefore larger than making a column look tidy. It protects the meaning that later calculations and reports depend on.

The first layer to consider is Power Query, Power BI’s data acquisition and transformation environment. If the requirement concerns the rows and columns that should enter the semantic model, Power Query is often the relevant place to inspect, standardize, filter, replace, or otherwise transform the query output. Those operations occur as part of data preparation and are applied again when the query is evaluated during refresh. The Power Query formula language is called M, although you don’t need to begin with formula syntax to reason about the requirement. Begin with the rule, identify the affected records, and decide what treatment preserves the intended meaning.

Other layers answer different questions. Semantic-model design governs structures such as tables and relationships. Data Analysis Expressions, usually called DAX, calculates results in model context.

The report layer governs what a consumer sees and how report interactions behave. The Power BI service governs operational concerns such as refresh and access state. These layers are connected, but they aren’t interchangeable.

Hiding an invalid status with a report filter doesn’t correct the query output. A DAX measure that counts missing identifiers can expose a quality problem, but the measure doesn’t by itself repair those records. Changing a visual also won’t correct a transformation that removed valid rows.

That gives you a practical decision rule. If the rule defines which source records and values are acceptable before modeling, inspect and enforce it in data preparation. If the observed problem concerns relationship behavior, verify the semantic model. If a calculation changes unexpectedly under a filter, inspect the DAX result and its context.

If only one report interaction is wrong, investigate that report behavior. If the prepared data and model are correct but a refresh or access condition differs in the service, validate the service state. Correctness should be demonstrated where the behavior actually occurs, not inferred from a successful action somewhere else.

Return to the sales data. Imagine that the analyst standardizes order-status labels and removes rows with missing order identifiers. The interface accepts both operations, and the query preview looks cleaner.

That still doesn’t prove the treatment was correct. Was a missing identifier grounds for deleting the entire record, or should the record have been retained and flagged for review? Did standardization combine genuinely equivalent labels, or did it collapse two statuses that the business distinguishes?

The tool can carry out either decision. It can’t supply the missing business rule. The analyst must document what rule is being enforced and why the chosen operation represents it.

Documentation doesn’t need to become a long policy document for every query step. It does need to make the decision testable. A useful rule might state that every loaded row requires an order identifier, followed by the approved treatment for rows that don’t meet that condition.

Another might identify the permitted status labels and define how known variations map to them. For uniqueness, the note should identify the relevant key and grain. Saying that duplicates were removed is incomplete if nobody can tell which columns defined a duplicate or whether repeated order identifiers were expected across product lines.

After each material cleaning operation, compare row counts and distributions. A material operation is one that could meaningfully change which records remain or how values are categorized. If filtering missing identifiers reduces the query from fifty thousand rows to forty-nine thousand eight hundred, the two-hundred-row difference is evidence to investigate.

It might exactly match the number of records that failed the documented rule. If it removes two thousand rows instead, you have an unexpected result even though the step itself completed. The before-and-after counts help separate intended cleaning from accidental data loss.

Distributions provide another kind of evidence. Suppose three approved status categories existed before standardization, along with two known spelling variations. After applying the mapping, the two variations should disappear, while their records move into the intended approved categories. If an approved category disappears as well, the transformation may be too broad.

If an unexpected fourth category remains, the mapping may be incomplete. You’re not merely checking whether the column looks cleaner. You’re comparing the observed result with a prediction derived from the rule.

Power Query includes Column quality, Column distribution, and Column profile for examining data. These tools support related but different views of a column’s condition and value patterns. They can help you find empty, unexpected, or repeated values, inspect how values are distributed, and look more closely at a selected column.

Their real value comes from asking a specific question before interpreting what they show. If the requirement says status belongs to an approved domain, inspect the values and their distribution against that domain. If an identifier should be unique at a stated grain, inspect whether repetition agrees with that grain.

There’s an important qualification when using those profiles in Power BI Desktop. By default, profiling is based on the first one thousand rows unless you select profiling for the entire dataset. That means a clean-looking profile may describe only the initial sample. Imagine that the first unexpected status appears on row fifteen hundred.

The default profile can look completely acceptable while the full query contains a violation. Switching to entire-dataset profiling changes the evidence available to you. It doesn’t change the business rule, and it doesn’t automatically clean anything. It allows the check to consider records outside the initial one-thousand-row profile.

This explains a common troubleshooting pattern. A report works for familiar data and then fails when a different data condition arrives. The analyst should reproduce that condition and write down the expected result beside the actual result.

If a newly arrived status should map to an approved category but instead appears separately, inspect the query step responsible for status standardization. Don’t begin by rearranging the visual, changing a relationship, or rewriting a measure unless the evidence points there. Those changes affect other mechanisms and can hide the symptom without correcting its cause.

After correcting the query logic, test both the original case and the edge case. The normal records should still map as intended, and the previously failing value should now receive the documented treatment. Then check the total row count and the status distribution again.

A correction that handles the new label but accidentally changes existing labels isn’t complete. Neither is a correction that produces the desired chart while altering the wrong query rows. Retesting both conditions protects against replacing one problem with another.

Now consider uniqueness. The sales table contains several product lines for one order, so order identifier alone repeats legitimately. An analyst who selects the identifier and removes duplicates could discard valid lines. The familiar feature works exactly as requested, but the request supplied to it was wrong.

Before enforcing uniqueness, identify what one row represents. If one row represents an order line, the uniqueness rule may require an appropriate combination of fields defined by the business requirement. If the source doesn’t provide enough information to define that grain confidently, the safe response is to surface the uncertainty rather than invent a key rule.

Completeness requires the same care. A blank order date may violate a rule because the date is required for every loaded transaction. A blank region might be allowed while assignment is pending. Treating every null value as an error ignores those different meanings.

Treating every null as acceptable is equally weak. Profile the relevant columns, compare the empty values with the documented requirement, and choose a treatment for each rule. Depending on the approved business decision, that treatment could preserve, replace, filter, or flag affected records. What matters is that the operation follows the rule instead of a blanket preference for eliminating blanks.

Data types also deserve deliberate validation. A column’s displayed values can appear plausible while its type doesn’t represent the business meaning you expect. The quality check asks whether the selected type supports the intended use before the column reaches downstream calculations. After a material type-related transformation, inspect the query output and its profile rather than assuming that acceptance by the interface proves every source value was handled as intended. The next refresh may contain a value pattern that wasn’t present in the data you first examined, which is another reason the rule and its validation need to survive beyond one successful preview.

Value distributions can expose problems that individual rows don’t make obvious. Suppose nearly all sales records historically use the approved regions, but the refreshed distribution suddenly shows a large blank category. A blank region is permitted in this scenario, so the presence of blanks isn’t automatically a violation.

The unusual proportion still deserves investigation because it may indicate a changed source process or incomplete assignment. A distribution check doesn’t decide correctness by itself. It tells you where observed data no longer matches the pattern or expectation that should be reviewed.

The order of transformations can also affect what your evidence means. If you inspect a distribution only after filtering invalid rows, you can confirm the condition of the retained output, but you may lose sight of how many source records failed. That’s why before-and-after counts matter.

Capture enough evidence to explain both what entered the operation and what left it. When several material operations occur, validate after each one rather than waiting until the end. Otherwise, a final row-count difference can tell you that something changed without identifying which operation caused it.

Suppose the prepared query now contains only approved status labels and every row has the required identifier and date. The analyst loads the data, builds the model, and sees sensible totals. Validation should continue into the layer where each downstream behavior occurs. Confirm the query output for the cleaning rules.

Confirm relationship behavior in the semantic model if the requirement depends on how tables connect. Confirm a DAX calculation through its actual result and filter context. Confirm report behavior through the relevant interactions. Confirm refresh or access conditions in the service when those operational states are part of the requirement.

This doesn’t mean every quality question must be tested in every layer. It means the evidence must match the claim. A Power Query profile can support a claim about prepared column values. It can’t, by itself, prove that a DAX result behaves correctly under every filter.

A correct DAX total can’t prove that no invalid source categories remain, because the calculation might group or exclude them. A successful service refresh confirms an operational outcome, but success alone doesn’t establish that every business quality rule was met. Ask what you’re claiming, then observe the layer that can actually prove it.

When the result differs from the requirement, resist changing several layers at once. Reproduce the failing data, filter, refresh, role, or interaction condition. Record what should happen and what actually happens. Identify the setting, query step, model relationship, calculation context, report interaction, or service state that directly controls that behavior.

Make the smallest relevant correction, then retest the ordinary case and the edge condition. This approach preserves a chain of reasoning from requirement to mechanism to evidence. It’s much more dependable than trying familiar features until the report looks right.

For the sales solution, the finished evidence might include documented domains and completeness rules, profiles based on the intended data scope, and row counts captured around material cleaning operations. It could also include the observed distribution before and after status standardization, plus retesting of the unexpected label that originally failed. Together, those observations explain not only that the output changed, but why it changed and whether the change matches the business decision. If the report later receives another unfamiliar value, the analyst has a defined rule and a repeatable check rather than an undocumented collection of cleanup steps.

A functioning report is therefore only the beginning of the quality decision. The stronger question is whether the prepared data, model behavior, calculation, report interaction, or service state can be shown to meet its own requirement. For data quality rules, start with the meaning of a valid record.

Test domains, uniqueness, completeness, data types, and distributions before modeling. Apply the treatment at the layer that owns the behavior. Then use row counts, profiles, distributions, and the appropriate downstream observations to prove that the intended result survived the entire path to consumption.

Data quality isn’t established merely because Power BI loads the data or a chart produces a plausible total. It begins with a specific rule about valid domains, uniqueness, completeness, data types, or expected distributions. That rule tells you which layer owns the decision and what evidence can confirm it.

In Power Query, profiles, full-dataset checks, row counts, and before-and-after distributions can show whether a material cleaning operation behaved as intended. Downstream results still need validation in the semantic model, calculation, report, or service layer where they occur. When you can connect the requirement to the responsible mechanism and then to an observable result, your cleaning process becomes explainable, testable, and repeatable rather than a collection of settings that happened to work once.

State one data quality rule in business language before choosing a Power Query action. What failing row and passing row would demonstrate that the rule is implemented correctly? Review the episode reference for the source-specific notes and a practical sequence for checking your result. This lesson was produced by Bare Metal Cyber dot com.

Diagnosing Data Quality Issues Before Modeling

headphones Listen Anywhere

More Options »
Broadcast by