Evaluating Data with Statistics and Column Profiles
A sales report refreshes successfully, its visuals appear normal, and nobody sees an obvious warning. Is that enough evidence that the incoming data is suitable for analysis? Not necessarily. A successful refresh tells you that a process completed. It doesn’t prove that an identifier is consistently populated, that a supposed category contains only the expected values, or that troublesome records aren’t hidden farther down the source.
The PL-300 scope includes evaluating data, including data statistics and column properties. That wording matters because evaluation comes before confident use. You’re not merely looking for a button that sounds related to data quality.
You’re trying to gather evidence about the contents of each column and compare that evidence with what the business says the column should contain. Power Query, Power BI’s data acquisition and transformation environment, provides the profiling tools for this work. Those tools are Column quality, Column distribution, and Column profile.
Suppose a sales and operations team gives an analyst a table containing Order ID, Order Date, Region, Sales Amount, and Order Status. The team says every order should have an identifier and date. Region should use a controlled set of names, while Order Status should come from a short list of business states.
Sales Amount should be usable for analysis. Those statements are requirements. They’re more useful than a vague request to check the data because they tell the analyst what assumptions need evidence.
The relevant Power BI layer is Power Query because the immediate question concerns the data being acquired and prepared. A semantic model organizes data for analysis. DAX calculations produce results in a model context. Report interactions affect what a consumer sees, and the Power BI service governs other parts of the published experience.
None of those layers substitutes for inspecting the incoming column characteristics in Power Query. A report filter might hide a questionable record, but hiding it doesn’t evaluate the source column. A measure might still produce a number, but that number doesn’t establish that every underlying value met the intended requirement.
Power Query profiling exposes several kinds of characteristics, including errors, empty values, distinct values, unique values, distributions, and statistical information. Each characteristic answers a different question. An empty result points toward missing content.
An error indicates that Power Query couldn’t produce an ordinary value for that entry under the query’s current handling. Distribution helps reveal how values are spread across a column. Statistical characteristics provide another way to examine whether the data resembles what the analyst expected.
Distinct and unique are especially easy to confuse. Distinct concerns how many different values appear. Unique concerns values that occur only once. Imagine that the Region column contains North, North, South, and West.
There are three distinct region values, but South and West are the values appearing only once in that four-row example. The difference becomes practically important when you examine an identifier. If every Order ID is meant to identify one record, repeated identifiers deserve attention. In a category such as Region, repetition is entirely expected, so a low number of distinct values isn’t automatically a problem.
Column quality gives a compact view of quality-related states such as errors and empty entries. Column distribution helps you evaluate the variety and repetition of values. Column profile provides more detailed characteristics for a column.
These aren’t three competing solutions where you must select one universally correct feature. They’re related views of the data. The useful choice depends on the question you’re asking.
To investigate missing Order IDs, quality information is directly relevant. To assess whether Region contains unexpected categories, distribution is useful. To inspect a particular column more closely, its profile supplies focused evidence.
That evidence still needs interpretation. An empty value may violate a requirement in Order ID but be permitted in a field the business considers optional. Repeated values may be a serious concern in a supposed identifier and completely normal in Order Status. A large number of distinct Sales Amount values might be reasonable because transactions differ.
The same pattern in a tightly controlled status column could challenge the stated assumption. Profiling exposes characteristics. The business requirement tells you whether those characteristics are acceptable.
There’s also an important limit on what you’re looking at. In Power BI Desktop, profiling is based on the first one thousand rows by default unless the analyst switches to profiling the entire dataset. Before treating the results as representative, check which basis is being used. This isn’t a minor display preference. It changes the evidence supporting your conclusion.
Return to the hypothetical sales table. Its first one thousand rows come from the current ordering system. Those rows contain complete Order IDs, expected region names, and no visible errors in Sales Amount.
Older records appear later in the dataset because they were migrated from a legacy system. Some of those later records contain empty identifiers, alternate region spellings, or values that don’t behave as expected under the query’s current handling. A profile limited to the first one thousand rows could look reassuring even though the full dataset contains the conditions the analyst needs to investigate.
If the analyst reports that the entire table is clean after viewing only the default profile, the problem isn’t that Column quality malfunctioned. The conclusion reached beyond the evidence. The tool described the rows it was profiling.
Once the analyst switches to the entire dataset, the observed characteristics can change because later rows are now included. That difference is itself useful troubleshooting evidence. It explains why one analyst might report no apparent issue while another, examining the complete dataset, finds errors or empty values.
Now suppose a colleague says that the report already works, so changing the profiling basis is unnecessary. That argument mixes layers. A normal report view may not exercise every record or expose every questionable value. Report success is evidence about that report state, not proof that the query output satisfies every data requirement.
The appropriate test begins where the behavior occurs. For this topic, inspect the Power Query profiling results and the query output. If a downstream result is also in question, validate that result in its own layer rather than assuming one successful observation proves the whole solution.
A disciplined investigation starts with an observable mismatch. Perhaps operations expects every Order ID to be present, but the full-dataset profile exposes empty entries. Perhaps the business recognizes four region names, while the distribution indicates more distinct values than that expectation would suggest.
Or perhaps the analyst sees errors in a column that was assumed to be ready for analysis. These observations don’t yet tell you every cause or prescribe a universal correction. They tell you which assumption failed and where to examine it.
The analyst can then compare a normal condition with the condition that exposes the issue. The first one thousand rows form one controlled view. The entire dataset forms another.
If the limited profile appears acceptable and the full profile doesn’t, the difference narrows the investigation to records outside the initial group. There’s no reason to begin by changing a visual, rewriting an unrelated DAX calculation, or adjusting service access. Those actions don’t govern which rows Power Query profiles, so they can’t explain this particular discrepancy.
After any correction to the relevant query or source handling, the analyst should repeat the same observations. Does the full-dataset profile now show the expected quality characteristics? Does the column’s distribution align with the permitted categories? Does the focused profile support the intended column assumption?
Then check the original normal case as well. Correcting an edge condition isn’t enough if the change causes previously acceptable records to behave differently. Verification covers both the condition that failed and the condition that already worked.
Notice what successful validation does and doesn’t establish. It can demonstrate that the inspected query output now matches the defined requirement under the tested conditions. It doesn’t mean every possible business rule has been discovered.
Profiling might show that Order ID has no empty entries, while the business could still have a separate concern about whether each identifier corresponds to the correct real-world order. Keep the conclusion proportional to the evidence. Column characteristics can challenge assumptions, but they shouldn’t be stretched into claims they don’t support.
This is why the requirement must come before the profile interpretation. If the business only says, “Make the data good,” there’s no precise result to verify. A better requirement states what matters. Order ID must be populated.
Region must use the organization’s expected categories. Order Status must follow its defined business states. With that information, the analyst can use quality, distribution, and profile evidence to determine where the observed data disagrees with the intended data.
Consider the Order Status column more closely. Repeated status values are normal because many orders can share the same state. A profile showing repetition therefore isn’t proof of duplicate orders. What matters is the set and distribution of status values compared with the expected business states.
If the distribution suggests an extra variant, such as two differently represented forms intended to mean the same thing, the analyst has found an inconsistency worth investigating. The profile reveals the pattern. The requirement supplies its meaning.
Now compare Order Status with Order ID. The same repetition that’s normal for status may challenge the intended role of an identifier. This contrast is why a generic rule such as “duplicates are bad” fails.
Data quality depends on column purpose. Distinct and unique characteristics give you evidence, but you still need to know whether the column represents a category, an identifier, a measurement, or something else within the business requirement. A familiar profiling result can mean something very different when the column’s purpose changes.
The same caution applies to empty values. An empty optional comment may be harmless. An empty required identifier may prevent a record from satisfying the stated requirement.
Don’t treat every empty cell as equally serious, and don’t dismiss all empties because some are allowed. Identify the affected column, restate its intended property, and evaluate the observed empty entries against that property. This keeps the decision grounded in business meaning rather than an arbitrary demand that every field contain something.
Errors also need layer-aware investigation. If Power Query exposes errors, that’s a query-level observation. A report visual may display only unaffected data or may not make the cause obvious. The analyst should inspect the query behavior associated with the affected column rather than trying to repair the symptom through report formatting.
Once the cause is corrected, the evidence should appear where the issue was found. The profile and query output should change in the expected way. A visual looking better can be an additional downstream check, but it isn’t a replacement for query-level verification.
This distinction protects the rest of the solution. Data prepared in Power Query feeds later modeling and reporting work. If an analyst accepts an unrepresentative profile, questionable records can travel forward before anyone recognizes the failed assumption. DAX logic or report filters might then produce apparently reasonable results for common selections while a different data condition exposes the weakness. Evaluating the columns early doesn’t guarantee every downstream calculation, but it gives later work a better-defined input and records what was actually checked.
Suppose the analyst corrects the sales query and sees no errors while still profiling only the first one thousand rows. That isn’t sufficient if the reported problem occurred in an older migrated record beyond that boundary. The analyst must reproduce the relevant data condition. Full-dataset profiling provides the appropriate basis for evaluating the entire table. The original limited view can still be useful as a comparison, but it can’t validate a correction aimed at records it never included.
The reverse mistake is possible too. An analyst might see a concerning full-dataset characteristic and immediately make a broad change without identifying the affected requirement. That can create a new side effect or erase legitimate variation. First determine what the profile reveals.
Then connect that observation to the column’s intended purpose. Only after that should a correction be chosen and tested. Profiling supports a decision. It doesn’t remove the need for judgment.
When the correction is complete, capture concrete before-and-after evidence in the relevant layer. Before the change, the full-dataset profile might expose empty Order IDs or unexpected distribution in Region. After the change, repeat the full profile and compare it with the exact requirement.
Inspect the query output that previously demonstrated the issue. Then confirm that the normal records still behave as expected. This is stronger than saying the settings saved successfully because it verifies the behavior rather than the act of configuration.
If a downstream report still produces an unexpected result after the query output is correct, the investigation can move to the layer that owns that remaining behavior. The semantic model, DAX result, report interaction, refresh state, access state, and consumer experience are separate places to validate when they’re relevant. Don’t keep modifying Power Query merely because that’s where an earlier issue was found. Once query-level evidence satisfies the data requirement, a remaining report or service symptom must be examined in the context that directly controls it.
That layer-by-layer approach prevents two opposite errors. The first is assuming a clean-looking report proves the data preparation is sound. The second is assuming a corrected query proves every model, calculation, interaction, refresh, and access condition is also correct. Each result needs evidence from the place where it occurs. In this episode’s scope, the central evidence comes from Power Query’s quality, distribution, and profile information, with the one-thousand-row or entire-dataset basis made explicit.
For the sales and operations solution, the analyst can now make a defensible statement. The columns were evaluated against defined business expectations. The profiling basis was checked rather than assumed.
Any discrepancy between the initial rows and the complete dataset was reproduced and investigated. The correction was verified in the query output under the condition that originally failed, and the normal condition was retested. That’s much stronger than saying the profile looked fine or the report happened to load.
This habit carries into later preparation work. When profiling reveals empty entries, unexpected values, errors, or inconsistent distributions, it gives you a focused starting point for deciding what needs attention. The next decision should follow from the requirement and the observed data, not from a reflex to apply a familiar feature. Profiling is most valuable when it turns an assumption about a column into something you can inspect, question, and verify.
Column quality, Column distribution, and Column profile help you replace guesses about incoming data with observable characteristics. Their results become useful when you compare them with a specific requirement, such as whether an identifier must be populated or a category must use an expected set of values. Just remember that the evidence is only as broad as the rows being profiled. A reassuring view of the first one thousand rows doesn’t establish the condition of the entire dataset.
Check that basis, investigate discrepancies in Power Query, and retest the query output under both the failing and normal conditions. When another symptom remains downstream, validate it in the layer that controls it. That combination of requirement, representative evidence, and layer-specific verification turns profiling into a defensible analytical decision.
Name the different questions answered by Column quality, Column distribution, and Column profile. Why might a clean first one thousand rows still be insufficient evidence? Use the matching episode reference as the visual and technical handoff for practice with this concept. This lesson was produced by Bare Metal Cyber dot com.