All articles

KajoData / Career & business

Power Query Column from examples: turn a pattern into a rule

I show how to choose examples, read the proposed transformation and distinguish an inferred pattern from an agreed business rule.

Kajo RudzińskiPublished Updated 12 min read

Example blocks pass an inspection station and form a new column.

You know that PL-0042 should produce PL, but you cannot remember the corresponding M function. Column from examples lets you enter an expected result and inspect a proposed transformation. That is a useful starting point, provided you check which rule the program actually records.

I treat this feature as help expressing an intention I already understand. Several plausible results do not establish that the complete process is correct. Here I build a small example, choose the input columns and separate a successful pattern suggestion from an agreed business rule.

Define the output before entering examples

Consider a synthetic shipment list. Code contains a country prefix and a local identifier separated by a hyphen. For this exercise, I want a new Country column containing the text before the first hyphen. I am not yet validating the prefix against an official country list or checking that the shipment exists.

Code Expected Country
PL-0042 PL
DE-0081 DE
FR-0017 FR
PL-AB-22 PL

The final row matters. It demonstrates that the local identifier can contain another separator. The first-fragment rule still gives PL. A rule taking everything before the last hyphen would instead give PL-AB. Both rules agree for the first three rows and diverge on the fourth.

I therefore write the rule as a full sentence. This prevents me from quietly adapting the requirements to whatever formula appears. The program can suggest a transformation, but it does not know the agreement between the file supplier and the report reader. Establishing that agreement remains part of my work.

Select only the inputs the result needs

In the Power Query editor, I select Code and choose Add column, Column from examples, From selection. I enter PL beside the first code and DE beside the second. Then I inspect the suggested results and transformation text.

Microsoft’s documentation confirms the selection and all-columns options, the proposed formula preview and the new query step added on confirmation. I can use the feature without remembering the menu location of every text operation.

All columns is appropriate when the output actually depends on multiple fields. For a code prefix, I avoid bringing account manager, region and row number into the inference. In a tiny sample, those values might happen to agree. The next file can break that coincidence and expose a rule I never intended to use.

Before continuing, I ask whether another person could explain why each selected input is relevant. If the answer is merely that it was already highlighted, I narrow the selection. The exercise becomes easier to inspect when unrelated fields are left outside it.

Read the proposal as well as the results

For PL-0042, taking the first two characters and taking the text before a separator produce the same output. That makes the formula important. If the agreed format allows prefixes of different lengths, a fixed two-character rule does not express my intention.

Text.BeforeDelimiter provides a reference for the rule used here. This expression shows the meaning I want to compare with the proposal. I do not promise that every application version will generate an identical query step.

Text.BeforeDelimiter([Code], "-")

PL-0042 gives PL because the desired text precedes the first separator. PL-AB-22 also gives PL. Choosing the first occurrence is part of the requirement, rather than an incidental implementation detail.

When the preview proposes a different rule, I add an example that distinguishes the alternatives or choose an explicit menu transformation. I do not keep negotiating with the suggestion merely to preserve the appearance of automatic generation. A readable, repeatable step is the intended outcome.

Choose examples that distinguish possible rules

For a separate synthetic task, suppose I want the description after the first hyphen in A-note. One example cannot distinguish removing two initial characters from extracting text after the separator. I add XYZ-note and expect the same description.

That example is more informative than ten nearly identical values starting with one letter. I select cases according to the differences between plausible transformations. If several separators occur, I include a case that establishes whether the first or last one matters.

The Text.AfterDelimiter reference explains the operation and the optional occurrence selection. It lets me explain a result without relying solely on what appeared in a preview window.

This approach does not imply that one inferred rule can parse any possible input. If the field mixes several formats, I first need a way to distinguish them. A single example-driven column may cover only part of that larger problem. Recognising the limit is more useful than adding increasingly complicated examples without a clear specification.

Remember the first-100-rows boundary

Column from examples uses the first 100 rows of the preview. Microsoft states that limitation in the feature documentation. An unusual format appearing later may never be represented in that editing experience.

I prepare a small variety of cases: an ordinary code, a long identifier, multiple separators, a missing value and a different notation. This selection helps establish the rule. It does not demonstrate that every record in the full source satisfies that rule.

After accepting the transformation, I return to the complete scope. I inspect exceptions, errors and the output type. An attractive first page of results is an initial observation. A recurring report also needs a decision about values arriving in the next file.

I do not remove difficult records from the target dataset simply to improve the preview. A sample helps expose differences to the suggestion process. Handling missing or malformed input requires a separate decision. Otherwise, the apparent success may consist of making the problematic shipments disappear.

Ordinary blocks and an unusual piece are compared with two mechanical templates.

Decide how to treat missing values and whitespace

Next I add a missing code and a code surrounded by spaces. I ask whether leading and trailing spaces are incidental formatting or meaningful parts of the identifier. I avoid applying the answer for one field automatically to every other field.

When the intention is to remove surrounding whitespace, I use the Text.Trim reference. Trimming the edges differs from removing every space inside a value. An internal space in a product name can be necessary; one inside a code may need investigation.

During inspection, I keep the original column beside the result. If an output is empty, I want to distinguish missing input, removed content and an error. Those cases become harder to reconstruct after the source value has disappeared from view.

For a missing code, I agree an explicit outcome, such as no country value plus a separate exception list. I do not assign PL merely because most other shipments use that prefix. Doing so would introduce information that the source never supplied. The decision should follow the field definition rather than the majority of the sample.

Combining fields justifies selecting several columns

For another independent task, I have City and Warehouse and need a display label. I select both fields and enter an example such as Kraków / Central. I do not use the display label as a warehouse identifier because descriptive names can change.

The Text.Combine documentation helps interpret a possible solution. The function joins a list of text values using an optional separator and ignores null values. That behaviour matters when one source field is missing.

If a missing Warehouse should leave only the city, this may be an appropriate direction. If missing warehouse information should stop preparation of the list, silently omitting it would conceal a problem. A technically valid label can still omit information that the reader needs.

I also decide whether an empty name means the same thing as null. The requirement includes expected outputs for both. That turns a vague conversation about whether the separator looks right into a concrete specification that another person can inspect.

Write business thresholds explicitly

Classifying amounts from a handful of examples can make a convenient demonstration. It cannot uniquely establish exact business boundaries. Observations at 3000, 8000 and 12000 do not tell the program that the agreed cutoffs are 5000 and 10000.

For a synthetic exercise, I can define low as up to and including 5000, medium as up to and including 10000, and high as above that. I then use an explicit conditional column and inspect the boundaries. Microsoft documents conditional columns, including the order in which conditions are evaluated.

I choose 4999, 5000, 5001, 9999, 10000 and 10001. Under the stated rule, the categories are low, low, medium, medium, medium and high. These expected results follow directly from the definition and can be reasoned through without running the application.

Missing amounts and negative values need separate decisions. Should a return belong to the lowest band or to a distinct category? Until that is answered, a correctly transcribed threshold rule still leaves the business question incomplete. These arbitrary teaching thresholds carry no medical or financial significance.

Treat data type as part of the rule

Before comparing an amount with a cutoff, I check that the amount is numeric. A value that looks numeric in a text column needs a different treatment. For identifier columns, I may specifically need text, particularly when leading zeroes carry information.

Microsoft’s data type guide describes column types and the role of locale in conversion. Decimal comma and decimal point conventions should be established from the source format rather than guessed from my usual spreadsheet settings.

I ask the source supplier which representation is agreed. A teaching example names its type and separator explicitly. A recurring business process should preserve the interpretation so that someone refreshing it on another computer knows what the conversion expects.

Converting an identifier to a number and then back to text does not reliably restore lost leading zeroes. This is a useful reason to inspect the rule before the resulting field becomes a join key. Once information has been discarded, a later formatting choice cannot prove what was originally present.

Inspect errors before replacing them

When a step introduces errors, I locate a representative record and read the details. Microsoft distinguishes step-level and cell-level errors. A missing column after a source change and one malformed value need different responses.

I avoid immediately choosing Remove errors. In a shipment list, that could discard exactly the records operations needs to investigate. I prefer to establish the scope, keep the relevant identifier and decide whether to correct the format, adjust the rule or pass on an exception list.

Column profiling supports that inspection. Its default scope is the first 1000 rows, with an option to use the entire dataset. That differs from the first 100 rows used by Column from examples, as explained in the profiling documentation.

I keep those boundaries separate. A vague statement that Power Query uses a sample says too little. I need to know which feature inspected which records before claiming that a particular exception has been covered. That also makes the remaining uncertainty easier to explain to the report reader.

Understand what happens when the next file arrives

For the shipment exercise, imagine a second file containing ES-0100 and DE-AB-04. The required country values are ES and DE under the first-separator rule. I write those expected outputs before examining the next preview. Familiar-looking results should not be their own evidence.

Next consider ES0100, with no separator. That value violates the format used in the exercise. I do not quietly invent a fallback merely to keep the output column filled. I ask whether the source changed its format or whether the particular record needs correction.

A change in output can therefore have several causes: a new valid identifier, a malformed record, or an actual revision of the business rule. I keep those explanations distinct. Re-entering examples each time without understanding the stored transformation would make it difficult to tell which change had happened.

Separate display labels from keys

Kraków / Central may be a useful display label without uniquely identifying a warehouse. Similar names can describe different facilities, and names can change after a reorganisation. Before using the generated label as a key, I ask for a stable identifier.

In the shipment example, the extracted country groups many shipments. That is appropriate for country-level analysis but insufficient for matching an individual shipment to its status. Correct-looking text can serve one grain and fail at another.

I establish this before removing source columns. If a later step needs the complete shipment code, a correct Country field cannot replace the discarded identifier. Preserving relevant inputs remains part of preparation even when the exercise began with learning one menu command.

Leave a step another person can understand

After confirmation, I name the column according to its meaning. Country from code prefix is more useful than Custom. I also give the step a descriptive name so that its purpose is recognisable later. The name helps navigation; the formula still defines the transformation.

During review, I keep Code beside the result. I postpone removing unnecessary columns until I have checked downstream dependencies. When someone reports an unusual value, I want both the original input and the transformation output available for inspection.

If the new field will join a reference table, I also check completeness and the required uniqueness. Extracting PL does not establish that the reference table contains it or that a match will be unambiguous. My Power Query merge guide develops that next step.

Automation repeats recorded logic, including mistakes. I consider the task complete when I can explain the rule using ordinary and unusual inputs, rather than when the application announces that a column was added.

Exercise: distinguish the pattern and inspect a new file

Prepare the four codes from the opening table. Write expected results for text before the first separator and before the last separator. Choose one rule, use Column from examples and compare the proposed transformation with the requirement.

Add a longer prefix, a missing value and surrounding spaces. Decide what each should produce. Every additional example should resolve a named ambiguity; entering more rows without that purpose can leave the central uncertainty untouched.

Finally, prepare another list with new identifiers in the agreed format. Inspect its results without manually repairing output cells. If amount thresholds are needed, create a separate conditional column with explicit boundaries. Once the data is prepared, continue with Power BI filters and slicers to investigate how a reader’s selections affect totals.

Key takeaways

Does Column from examples inspect the complete source?

The feature uses the first 100 rows of the preview. After creating a transformation, inspect the complete scope and relevant exceptions separately.

Can a few amounts establish exact business thresholds?

They do not uniquely determine the boundaries. I define thresholds explicitly, use a conditional column and check boundary values and missing amounts.

Why read the generated M formula?

Different rules can agree on simple examples. Reading the transformation distinguishes a fixed character count from text extraction at a separator.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.