Creating and Transforming Columns in Power Query
A sales analyst creates a new column in Power BI, sees the expected values, and declares the work finished. But what would actually prove that the approach is correct? The presence of a column isn’t enough. You need to know whether the column was created in the right layer, when its values are evaluated, whether those values survive the next refresh, and whether the result still behaves correctly when the source contains an unexpected row. A configuration that works once can still be the wrong solution to the business requirement.
Creating and transforming columns is explicitly included in the skills measured for the PL-300 version covered by this season. The practical skill isn’t merely finding a command that adds or changes a column. It’s translating a requested outcome into a repeatable data-preparation operation. That starts by describing the outcome clearly.
What should each row contain when preparation is complete? Which source fields determine that value? Should the result remain stable while someone filters and explores a report, or must it respond dynamically to that interaction?
Suppose a company receives sales records from several operating regions. Each row contains a region code and an order identifier. The reporting team wants a stable row-level label that combines those source values so an order can be recognized consistently downstream.
The requested label doesn’t depend on which report page is open or which customer has been selected. It comes directly from the contents of each source row. That makes it a data-shaping requirement rather than a calculation that needs to respond to report interaction.
Power Query is Power BI’s data acquisition and transformation environment. A custom column created there, and an existing column transformed there, are evaluated during refresh. Power Query takes the incoming data, applies the recorded transformations, and produces shaped query output.
That output is then available to the semantic model. When someone later queries the model through a report, Power Query doesn’t recreate the value in response to each report filter. The value was prepared as part of the refresh process.
That timing is central to the decision. The order label in this example can be produced from the region code and order identifier during refresh because it represents a stable property of the row. If the source values change and another refresh runs, the transformation is evaluated against the refreshed data. If a report user merely changes a filter, the transformation isn’t reevaluated for that filter. The semantic model is queried using the prepared result it received.
This also explains why starting with a memorized button path is risky. Power BI offers more than one place to create calculations, including Power Query and Data Analysis Expressions, usually called DAX, in the semantic model. Those options don’t have identical evaluation timing or purpose. A feature isn’t automatically appropriate just because the interface allows you to create the desired-looking column. You first decide whether the requirement is stable refresh-time shaping or behavior that belongs to another layer.
The requirement for the order label points toward Power Query because the output is row-level, repeatable, and derived from source data before the semantic model is queried. Keeping that stable shaping in the source or in Power Query can also keep model logic simpler. In suitable circumstances, a transformation can benefit from query folding, where supported work is handled through the source query. That benefit depends on the source and the transformation sequence, so folding shouldn’t be assumed merely because Power Query is being used.
Now compare that label with a result that must change when a user selects a year or product category. A refresh-time custom column won’t recalculate because a report filter changed. It contains the result produced during data preparation. If the business meaning depends on the current query or filter context, putting it in a Power Query column would give it the wrong evaluation behavior. The displayed number might look correct under one filter and then expose the mismatch under another.
This is why a normal-looking report isn’t sufficient evidence. Imagine that the order label works for records where both source fields are populated. During the next refresh, a new region sends an order identifier in an unexpected condition.
The refreshed query output no longer matches the agreed format for that row. Changing a visual won’t repair the cause because the visual doesn’t own the transformation. The investigation belongs in the query steps and the resulting prepared data.
A disciplined investigation begins with the observable difference. State the expected output for the affected source row, then record the actual output after refresh. Confirm which source values reached Power Query and identify the step that creates or transforms the column. This narrows the problem to the mechanism that owns it. Randomly changing model calculations, relationships, or report formatting can make the report look different without proving that the refresh-time shaping has been corrected.
The same discipline applies when an existing column is transformed rather than a new one being added. The business may want an existing regional value prepared consistently before it reaches the semantic model. The transformation becomes part of the query’s repeatable sequence. On refresh, the source data enters that sequence and the resulting column appears in the query output. The meaningful test is whether representative input conditions produce the required output, not whether the transformation step was accepted without an error.
After correcting the responsible query step, test both the row that originally worked and the row that exposed the failure. The first test protects against a regression in the normal case. The second proves that the correction addresses the edge condition. Then refresh the solution and inspect the prepared result again. You’re looking for observable agreement between the requirement and the output, rather than relying on the fact that Power BI allowed the change to be saved.
Return to the sales-and-operations data. The reporting team now needs two prepared columns. One is the stable order label created from row values. The other is an existing operational category that needs a consistent business representation before modeling.
Both requirements describe row-level shaping that should be repeated whenever data is refreshed. They don’t ask for values that vary with a consumer’s report selections. That shared evaluation requirement makes Power Query a reasonable owner for both operations.
Before applying either operation, the analyst writes down what a valid output row should contain. For the order label, that includes the source fields that contribute to it and the expected stable result. For the operational category, it includes the accepted representation after transformation. This isn’t unnecessary documentation around a simple click. Without a defined expected result, the analyst can’t distinguish a correct transformation from one that merely produces plausible text for the currently visible sample.
The analyst then considers the data grain. Each record represents an individual order, so the new label must also describe that individual row. The transformation shouldn’t accidentally turn a row-level requirement into a summary that depends on a group of orders. Keeping the grain clear helps separate preparation from later analysis. Power Query prepares a value associated with each refreshed row, while the semantic model and its calculations can use those prepared values when answering report queries.
The sequence of query steps matters to the observable result because a custom or transformed column is evaluated as part of that sequence. The analyst therefore checks that the source fields have reached the relevant step in the condition expected by the rule. The goal isn’t to memorize one universal ordering for every query. It’s to trace the specific requirement through the recorded transformations and confirm that the input available at the responsible step can produce the intended output.
Power Query’s formula language is called M. You may encounter M when defining or inspecting the logic behind a custom transformation. The important question remains larger than the syntax. Can you explain which input columns the logic uses, what value it should produce for a representative row, and when that logic is evaluated? Correct-looking M doesn’t compensate for placing a filter-responsive requirement in a refresh-time column, just as choosing the correct layer doesn’t excuse incorrect row logic.
Once the two columns are prepared, the analyst inspects the query output. A few familiar rows are useful, but they aren’t enough. The evidence should include different source conditions that the business rule is expected to handle.
For the order label, that means comparing source values with the resulting label. For the operational category, it means checking that each tested input receives the agreed transformed representation. Any unexpected output should be traced back through the relevant query step.
The analyst also confirms that the columns reach the semantic model as intended after applying the refreshed query result. This is a separate observation from seeing a preview during preparation. Query output establishes what Power Query produced. The semantic model establishes what became available for downstream use. If the output is correct in one layer but absent or wrong in the next, that difference helps locate the failure instead of encouraging another unrelated change.
Downstream results deserve attention because a prepared column can influence how later model logic and reports behave. If a model calculation uses the order label or operational category, an incorrect transformation can make the later calculation wrong even when its own expression is sound. Likewise, a report can present a misleading grouping because the prepared category values don’t match the business rule. Repairing only the final visual would conceal the upstream data-quality problem rather than correct it.
Refresh testing is especially important. A Power Query transformation that appears correct against one captured sample still needs to operate when refreshed data arrives. Because custom and transformed columns are evaluated during refresh, the refreshed query output is direct evidence of whether the mechanism ran against the incoming data.
A successful refresh tells you that processing completed. It doesn’t, by itself, prove that every resulting value satisfies the requirement. Completion and correctness are different claims.
That distinction prevents a common troubleshooting mistake. An analyst sees no refresh error and assumes the new column must be correct. Yet a transformation can complete and still implement the wrong business logic, use the wrong source field, or produce an unsuitable result for an edge condition. Validation must compare actual output with expected output. The absence of a technical failure is useful operational evidence, but it isn’t a substitute for checking the meaning of the data.
There also isn’t one universally correct location for every column-related requirement. Stable row-level shaping often belongs at the source or in Power Query when that placement keeps the semantic model simpler and, where available, can benefit from query folding. A calculation whose value depends on model query context has a different requirement. Choosing between them requires attention to evaluation timing, grain, surrounding model logic, performance considerations, governance, and the way the result will be consumed.
For the sales solution, the analyst can now justify the decision in plain language. The two prepared columns are derived from source rows, should be stable between refreshes, and need to be available before the semantic model answers report queries. Power Query evaluates the operations during refresh, so its mechanism matches those requirements. A DAX calculation or a report-only workaround would move the behavior to a different layer and could change when or how the result is evaluated.
If the finished report still shows an unexpected order label, the analyst follows the value through the solution rather than guessing. First, inspect the relevant refreshed query output. If it’s already wrong there, investigate the source values and the responsible query step.
If it’s correct there, confirm what reached the semantic model. If the prepared model value is also correct, then inspect the downstream calculation, filtering, or report behavior that displays it. Each observation eliminates layers that are behaving properly.
This layer-by-layer method also protects working parts of the solution. If Power Query has produced the correct value, changing its transformation introduces risk without addressing the demonstrated fault. If the query output is wrong, adjusting a report visual may hide one symptom while leaving every downstream consumer with faulty prepared data. Troubleshooting becomes faster and more defensible when each change responds to evidence from the layer that controls the observed behavior.
The final check returns to the business requirement. Are the required columns present in refreshed query output? Do their tested values match the defined row-level rules? Do those columns reach the semantic model in the expected form?
Does the downstream report use them without changing their intended meaning? Those observations show more than successful configuration. Together, they demonstrate that the transformation belongs in the chosen layer, executes at the expected time, and supports the result the business asked for.
Creating or transforming a column is only part of the decision. You also need to place that work where its evaluation behavior matches the requirement. Power Query is suited to repeatable, stable row-level shaping that runs during refresh and prepares data before the semantic model is queried. That doesn’t make it the universal answer for every calculation.
It makes it the right candidate when the timing, grain, and business meaning align. Finally, prove the result where the behavior occurs. Compare expected and actual query output, confirm what reaches the semantic model, and check the downstream result. That evidence turns a saved configuration into a transformation you can explain, troubleshoot, and trust.
Decide whether a requested column change belongs in the source, Power Query, the semantic model, or a measure. What maintenance or refresh consequence supports your decision? A focused checklist and the lesson's exact labels are available in the companion episode reference. This lesson was produced by Bare Metal Cyber dot com.