Cleaning Text, Numeric, and Date Columns
A sales report has refreshed without an error. Customer names appear, revenue totals are populated, and order dates are available. Does that prove the data preparation is correct?
Not necessarily. A name with an extra space can create an apparent duplicate. A number with more precision than the business expects can produce confusing categories.
A date stored or handled in an unsuitable form may not yield the year, month, or day information the report needs. A successful refresh tells you that a process completed. It doesn’t, by itself, prove that the resulting columns satisfy the business requirement.
The current Power BI Data Analyst certification scope includes creating and transforming columns. That’s broader than knowing where to find a command in the interface. You need to understand what the requested outcome is, which layer should produce it, how one transformation affects the next, and what evidence will demonstrate that the result is correct. Text, number, and date operations are especially useful for developing that judgment because a transformation that looks harmless can change grouping, comparison, calculation, and reporting behavior downstream.
Suppose a sales and operations team receives files from several regional offices. The customer field contains values such as Northwind Traders, NORTHWIND TRADERS, and Northwind Traders followed by an unwanted space. A product reference combines a family code and an item code in one text value. Sales amounts contain more decimal precision than the business wants to display in its reporting categories.
The order date is available, but the report needs separate attributes for year and month. These aren’t four unrelated interface tasks. They’re examples of source irregularities that need to become predictable, model-ready attributes.
Start by restating each requirement in terms of an outcome. Customer names need consistent case and no unwanted leading or trailing spaces. The combined product reference needs to be split, or the required component needs to be extracted. The numeric value needs an agreed rounding result.
The order date needs the required date parts. This wording gives you something testable. By contrast, a request to clean up the data is too vague. It doesn’t say which differences are errors, which distinctions must remain, or what the finished columns should contain.
The next decision is where the work belongs. Power Query is Power BI’s data acquisition and transformation environment. When the requirement is to turn source irregularities into reusable, prepared columns, Power Query is the natural layer to examine.
Its transformations shape the query output that continues into the semantic model. That differs from defining a calculation in Data Analysis Expressions, or DAX, inside the model, changing how a report visual presents a field, or controlling access in the Power BI service. Those layers can all affect what someone eventually sees, but they don’t perform the same job.
Consider the customer name. Standardizing case changes values such as NORTHWIND TRADERS and Northwind Traders into one consistent form. Trimming text removes unwanted space at the beginning or end. Those operations address different defects.
Changing case doesn’t remove a trailing space, and trimming doesn’t make capitalization consistent. If both defects exist, using only one operation leaves the other irregularity in place. The required result therefore determines which transformations are needed, rather than the availability of a familiar text command.
Sequence matters because every later transformation receives the values and types produced by the earlier steps. Imagine splitting a product reference around a delimiter, then extracting a component from one of the resulting text columns. The extraction step depends on the split having produced the expected pieces. If the source includes an edge value without the delimiter, that row may not behave like the typical rows. Looking only at well-formed product references could make the transformation seem correct even though the query output contains an unexpected result for the unusual value.
Types matter for the same reason. A date-part operation needs a value that can be treated as a date, while a numeric rounding operation needs a number. The useful principle isn’t to apply every conceivable type operation first.
It’s to arrange the transformations so each step receives predictable values and an appropriate type. If a later operation doesn’t produce the expected result, inspect what actually arrived from the preceding step. Don’t assume that a column’s appearance proves that Power Query is treating it as the kind of value you intended.
Now compare that query-time preparation with a nearby alternative. Suppose the business needs the cleaned customer name to be reused throughout the model by reports that consume the prepared data. Producing that standardized column in Power Query addresses the irregularity before the query output enters the model.
If someone instead adjusts a label in one report visual, the presentation may look better in that visual, but the underlying prepared column hasn’t been standardized. Another report or another use of the field can still encounter the original inconsistency. The visual change and the query transformation may look similar to one consumer, but they operate at different layers and have different reach.
A DAX calculation is also not interchangeable with every Power Query transformation. DAX calculations belong to the semantic-model layer and produce results in that context. Power Query shapes values as part of data preparation. The right choice follows the requirement.
If the task is to regularize incoming text, split a source field, round a numeric source value for a prepared attribute, or derive reusable date parts during preparation, examine Power Query. If the requirement concerns a calculation whose behavior depends on the semantic model or report filter context, that points toward a different mechanism. Picking a feature because you remember its interface location skips the most important decision.
Return to the regional sales data. The analyst applies the required text operations, separates the product reference, rounds the designated numeric field, and derives year and month from the order date. A normal row now looks correct. Before accepting the result, the analyst checks values that challenge the assumptions.
One customer name has leading space rather than trailing space. Another uses mixed case. One product reference lacks the usual delimiter.
A sales amount sits near a rounding boundary. The dates include values from different months and years. These rows reveal whether the transformations implement the stated rules or merely happen to work on an easy example.
Suppose the customer field still produces what appear to be duplicate names. The wrong response is to begin changing unrelated visuals, relationships, or report filters. First reproduce the symptom in the layer that owns the transformation. Compare the expected cleaned value with the actual Power Query output.
Then inspect the sequence of relevant text steps. Perhaps case was standardized but whitespace wasn’t trimmed. Perhaps the edge value contains an irregularity that the chosen operation didn’t address. Correct the responsible transformation, refresh the query result, and test both the original normal value and the value that exposed the failure.
That retest is important. Fixing the troublesome row alone doesn’t prove that the broader column still behaves correctly. A revised split or extraction operation could change previously valid product references. A different rounding decision could alter values that were already accepted.
A changed date operation could produce the desired month for one row while another date exposes a false assumption. Verification needs at least the condition that first worked and the condition that failed. You’re looking for evidence that the rule now handles the intended range of values without introducing an obvious side effect.
For text transformations, testable evidence is the resulting text. Check whether case is consistent where the requirement calls for consistency. Check whether unwanted outer spaces are gone. After splitting or extracting, compare the resulting components with known input examples, including an irregular one.
Don’t judge only by whether a new column exists. A column can be created successfully and still contain incomplete, shifted, or inconsistent values. The business outcome concerns the contents of the column, not the successful execution of the command that created it.
For number transformations, inspect values that make the rounding rule visible. A whole number won’t tell you whether a decimal transformation did anything useful. Choose examples with meaningful decimal portions and include values near the point where the rounded outcome changes.
Compare the actual query output with the expected business result. If the requirement and the observed output disagree, determine whether the wrong operation was chosen, the sequence supplied an unsuitable value or type, or the requirement itself needs clarification. Don’t hide that uncertainty by accepting whatever result the interface produced.
Dates need the same discipline. If the requirement calls for year and month attributes, verify dates from more than one month and more than one year. A single date can’t demonstrate that both parts are derived consistently. Inspect the query output and confirm that each date produces the expected components.
If an operation fails or yields an unexpected result, check the value and type delivered by the preceding steps. Deriving a date part isn’t just a matter of creating a column with a promising name. The values must correspond to the source dates that the business expects the report to represent.
Once the prepared columns enter the semantic model, downstream behavior offers another form of evidence. Consistent customer values should no longer be separated merely because of the text irregularities the transformations were meant to remove. Product components should support the intended use as distinct attributes.
Rounded values should match the approved preparation rule. Year and month attributes should correspond to the underlying dates. If the model or report reveals a disagreement, trace it back to the layer responsible instead of assuming every visible problem began in the report.
This layer-based approach prevents a common troubleshooting mistake. An analyst sees an unexpected report result and changes the first accessible setting. If the defect is in Power Query output, rearranging a visual doesn’t repair the prepared data. If the prepared data is correct but a model calculation behaves unexpectedly under a filter, repeating the text or date transformation may not address the real cause.
First capture the expected and actual behavior. Then identify whether the controlling mechanism is the query step, semantic model, DAX result, report interaction, refresh state, access state, or consumer experience. Inspect the layer that directly governs the symptom.
For this episode’s sales solution, imagine that year and month look correct in a simple report view, but a newly refreshed batch produces missing or unexpected date components. The earlier visual result isn’t sufficient evidence because the data condition has changed. Reproduce the issue in the query output for the affected rows. Check what value and type reach the date operation.
Correct the relevant preparation step rather than compensating with a visual label. Afterward, verify the affected batch and recheck previously valid dates. The proof is the observable result across both conditions, not the absence of an error message.
The same reasoning applies when a transformation works under one report filter but the consumer reports a different outcome elsewhere. A filter can change which rows are visible, exposing data values that weren’t present in the original check. That doesn’t automatically mean the filter is defective.
Examine the newly exposed rows. They may contain the unusual text pattern, product reference, numeric value, or date that the transformation never handled correctly. Reproducing the consumer’s condition helps separate a report interaction problem from a latent data-preparation problem.
A successful configuration is only one event in the process. You selected an operation and Power BI accepted it. Correctness requires a stronger chain of evidence.
The business rule is explicit. The operation belongs to the appropriate layer. The steps are ordered so later transformations receive predictable values and types.
Typical and edge rows produce the expected query output. The semantic model and report then consume those prepared columns in a way that remains consistent with the original requirement. If one link is missing, there’s still something to investigate.
Be cautious about the idea that one preferred feature must be right for every text, number, or date requirement. Power BI provides multiple places where data can be prepared, calculated, displayed, and governed because those activities solve different problems. Even within Power Query, two valid operations may serve different requirements. Standardizing case doesn’t replace trimming.
Splitting doesn’t necessarily replace extracting a needed component. Rounding is only correct when its result matches the defined numeric rule. Deriving a date part helps when that attribute is actually required. Availability isn’t justification.
You can make the decision more reliable by asking three connected questions. What exact value must exist when preparation is complete? Which Power BI layer owns the creation of that value?
What observation would convince you that normal and unusual rows both satisfy the requirement? For the regional sales data, those questions lead to concrete checks of cleaned customer names, separated product components, rounded numeric outputs, and derived year and month values. They also tell you where not to make the correction when the result is wrong.
This habit carries into more complicated Power BI work because prepared columns often become inputs to later modeling, calculation, and reporting decisions. A transformation isn’t isolated simply because it appears as one query step. Its output becomes someone else’s input.
Predictable text, numeric, and date values make those downstream decisions easier to interpret and test. Unexamined irregularities can survive a successful refresh and only become visible when a different row, filter, or consumer exposes them. That’s why the final check belongs in the actual query output and in the downstream context where the prepared attributes are used.
Text, number, and date transformations are correct when they produce the values the business asked for, not merely when Power BI accepts the configuration. Start with the required outcome, place reusable data preparation in the layer that owns it, and order the steps so each operation receives predictable values and types. Then inspect the evidence.
Check cleaned text, separated components, rounded numbers, and derived date parts in the query output, including rows that challenge your assumptions. Follow those results into the semantic model and report when necessary. That combination of requirement, mechanism, and observable verification lets you distinguish a dependable transformation from one that only appeared to work on the first example.
Describe one text, one numeric, and one date transformation without naming menu clicks. How would you test locale, blanks, and boundary values before accepting the result? Use the episode reference to review precise terminology and the checks that are easier to inspect than to hear. This lesson was produced by Bare Metal Cyber dot com.