Simple Data Preparation for Power BI Using Power Query: A Hands‑On Guide to “Column from Examples”

If you’re looking for simple data preparation for Power BI using Power Query, the Column from Examples feature lets you describe the result you want and Power Query writes the transformation for you—no M code required. In this guide I’ll show practical recipes (full name, weekday name, “first three letters,” and more), how to choose From All Columns vs From Selection, and why this approach has no performance penalty—because it ultimately generates standard M steps behind the scenes. You can use it in Power BI Desktop, Power Query Online (Dataflows Gen1/Gen2), and Power Query in Excel.

Video

Why “Simple” Data Preparation Matters

Every successful Power BI solution stands on clean, well‑shaped data. You might:

Remove columns you don’t need,
Filter rows to the relevant slice,
Merge/join tables,
Create derived columns (concatenate first/middle/last name),
Extract date parts (weekday, month, year),
Reshape text (trim, split, take first N characters), and more.

These steps live in Power Query, inside the Power Query Editor, before the data reaches your data model for relationships, DAX, and visuals. The Editor exposes many transformations across its tabs. It can be overwhelming when you’re new—or simply in a hurry. That’s exactly where Column from Examples shines.

Meet “Column from Examples”: Natural, Fast, and Surprisingly Smart

Column from Examples lets you show Power Query what you want by typing the desired result in a new column. Power Query reads your example(s), infers the logic, and writes the M formula for you. You can then accept it as a new step.

Conceptually, it’s like Excel’s Flash Fill—but taken to the next level for Power Query’s typed, step‑based transformation engine. It’s one of the easiest pathways to simple data preparation for Power BI using Power Query, especially when you’re not sure which button or function name will produce the outcome you have in mind.

Where to Find It (Power BI Desktop)

Load data and open Power Query Editor (Home ► Transform data).
Go to Add Column ► Column from Examples.
- Choose From All Columns when your intended output could rely on multiple columns.
- Choose From Selection when you already know which column(s) your output depends on (faster in wide tables).

Power Query then creates an empty “example” column at the end. You’ll type the output you want for one or more rows; Power Query shows a preview of inferred logic and the M it intends to generate.

Tip: If your dataset has hundreds of columns, prefer From Selection—it narrows the “search space” for Power Query and keeps the UI responsive.

Scenario 1: Building a Full Name (First, Middle, Last)—Even with Missing Middles

Let’s say your Customers table includes FirstName, MiddleName, and LastName, but you want a properly spaced Full Name. Here’s the Column from Examples flow:

Add Column ► Column from Examples ► From All Columns.
In the sample cell for Row 1, type the exact full name you want—e.g.,
John V Y Yang
(Use the casing you want; Power Query is case‑sensitive for text functions.)
Press Enter. Power Query generalizes the pattern to the whole column.
- If Row 1 isn’t enough, type an example for Row 2 or Row 3—especially when some records have no middle name. This helps Power Query distinguish “insert a space” from “insert nothing.”

When you accept the step, Power Query generates clean M code. Behind the scenes, it typically produces a formula that concatenates the three text fields with spaces, and often handles nulls. A robust hand‑authored version looks like this:

// FullName from first/middle/last with intelligent spacing
= Table.AddColumn(
    #"Previous Step",
    "Full Name",
    each Text.Combine(
            List.RemoveNulls(
                { [FirstName], [MiddleName], [LastName] }
            ),
            " "
        ),
    type text
)

Why this is a perfect tool for simple data preparation for Power BI using Power Query:
You didn’t need to remember Text.Combine, lists, or List.RemoveNulls. You just typed John V Y Yang, and Power Query inferred the transformation.

Scenario 2: Deriving the Weekday Name from a Date

Let’s say you want to know on which weekday a customer was born, using a BirthDate column.

Select the BirthDate column.
Add Column ► Column from Examples ► From Selection.
Start typing the weekday name for the first example row or choose from the suggestions Power Query displays (you’ll see options such as Day of Week, Day of Week Name, Start of Week etc.).
Confirm with Enter. Power Query applies it to all rows and writes the M.

A canonical M expression for this transformation is:

= Table.AddColumn(
    #"Previous Step",
    "Birth Weekday",
    each Date.DayOfWeekName([BirthDate]),
    type text
)

Pro tip: If your report will be consumed in different locales, document the regional setting your model uses—which affects date names and formats. It’s still “simple data preparation for Power BI using Power Query,” but being explicit avoids surprises for multilingual audiences.

Scenario 3: “First Three Letters” vs “Everything Before the Space”

Imagine you’ve already created Full Name, and now you want the first three letters as a code. Here’s where Column from Examples learns by contrast:

Select the Full Name column (optional but recommended).
Add Column ► Column from Examples ► From Selection.
In Row 1, you might type Joh for John V Y Yang.
Power Query could initially infer “everything before the first space”—which would produce John, not Joh.
Give it a second example from a different row (e.g., Eug for Eugene L Hung). With two examples, Power Query correctly infers “take the first three characters.”
Accept the step.

The M looks like:

= Table.AddColumn(
    #"Previous Step",
    "First 3 Letters",
    each Text.Start([Full Name], 3),
    type text
)

Pattern clarity matters. If your first example is ambiguous, provide one or two more examples. The feature is smart—but it becomes brilliant when you feed it clear contrasts.

“From All Columns” vs “From Selection”: Which Should You Use?

From All Columns
Use when the desired output might blend multiple inputs (e.g., full name composed of 3 different columns, or an ID that mixes text and numbers from adjacent fields). It gives Power Query the full context to infer a cross‑column pattern.
From Selection
Use when you know the output will rely on a specific subset (e.g., extracting the weekday from BirthDate, creating initials from Full Name). It’s faster and reduces confusion in wide tables.

Either way, this feature is a friend of simple data preparation for Power BI using Power Query—you’re focusing on what you want, not hunting through menus for how to do it.

No Performance Penalty: It’s Just M Under the Hood

A common question I hear: “Is Column from Examples slower?”
No. It simply generates the same kind of Power Query M transformation you’d write manually or click together via ribbon commands. Once added, it’s a standard, materialized step in your query. You can:

Inspect the M it produced,
Rename the step to something meaningful,
Modify the formula if you wish, or
Replace it later with an equivalent hand‑coded expression.

In other words, Column from Examples is not a “magic runtime engine.” It’s a code authoring accelerator for simple data preparation for Power BI using Power Query.

Where You Can Use It

Power BI Desktop (Power Query Editor),
Power Query Online in Dataflows (Gen1 and Gen2),
Power Query in Excel.

The feature is consistent enough that your muscle memory transfers easily between tools. If you build reusable data cleaning patterns for your organization, Column from Examples helps you codify them rapidly and teach teammates who prefer clicks over code.

Practical Tips for Better Results

Be precise with casing
Power Query text functions are case‑sensitive. Type examples using the desired case (e.g., “John V Y Yang” vs “john v y yang”). This helps the inference engine match your intent.
Use multiple examples when the pattern is ambiguous
If Power Query guesses wrong on the first try, add a second or third example—especially when some rows are edge cases (missing middle name, different delimiters, etc.).
Prefer “From Selection” in very wide tables
Selecting the relevant columns reduces the search space and keeps the UX snappy.
Look at the generated M
Click Advanced Editor or the formula bar to see what Power Query wrote. This is a great way to learn M organically and to spot easy improvements (e.g., adding List.RemoveNulls for safer concatenation).
Name your steps
Replace default step names (e.g., Added Custom) with descriptive names (Added Full Name). Your future self—and your teammates—will thank you.
Validate outputs
Quick filters, Keep Top Rows, or Group By Count can help you sanity‑check the derived column before you load the data.
Remember the built‑in equivalents
Once you know the exact transformation, you can also use ribbon actions like Add Column ► Extract ► First Characters. Column from Examples often helps you discover which built‑in to use next time.

Five Everyday Recipes You’ll Use Again and Again

Here are simple, reusable patterns that align with simple data preparation for Power BI using Power Query.

1) Full Name with Clean Spacing

= Table.AddColumn(
    #"Previous Step",
    "Full Name",
    each Text.Combine(List.RemoveNulls({[FirstName], [MiddleName], [LastName]}), " "),
    type text
)

2) Initials (F.M.L or F.L)

You can build this by example (e.g., type J.V.Y. for one row, E.H. for a row with no middle name) and let Power Query infer it.

= Table.AddColumn(
    #"Previous Step",
    "Initials",
    each
        let
            parts = List.RemoveNulls({[FirstName], [MiddleName], [LastName]}),
            letters = List.Transform(parts, each Text.Start(_, 1))
        in
            Text.Combine(letters, ".") & ".",
    type text
)

3) Weekday Name from a Date

= Table.AddColumn(
    #"Previous Step",
    "Birth Weekday",
    each Date.DayOfWeekName([BirthDate]),
    type text
)

4) First N Characters (Avoid Ambiguity)

= Table.AddColumn(
    #"Previous Step",
    "First 3 Letters",
    each Text.Start([Full Name], 3),
    type text
)

5) Everything Before the First Space

= Table.AddColumn(
    #"Previous Step",
    "First Token",
    each Text.BeforeDelimiter([Full Name], " "),
    type text
)

When you type examples, Power Query may toggle between #4 and #5 depending on your first example. Provide a second example to guide it correctly.

When Not to Use Column from Examples

While Column from Examples is fantastic for simple data preparation for Power BI using Power Query, there are cases where a direct transformation is clearer:

Relational ops like Merge Queries/Append Queries.
Typed conversions that must be explicit (e.g., numeric parsing with specific locales).
Complex conditional logic that’s easier to reason about in a Conditional Column or a hand‑written if … then … else expression.
Performance‑critical steps with very large datasets where you want complete control over function choice and buffering (e.g., Table.Buffer)—though remember, Column from Examples itself doesn’t slow things down; it’s the functions selected that matter.

That said, even for complex tasks, I often start with Column from Examples to bootstrap the M, then refine manually.

Real‑World Flow: From Raw Data to Model‑Ready

Here’s a simple end‑to‑end outline you can adopt on your next project:

Connect to your source (CSV, Excel, SQL, folder, etc.).
Profile quickly (Column Quality/Distribution/Profiling) to spot issues.
Remove noise (unneeded columns, irrelevant rows).
Derive fields using Column from Examples:
- Full names, friendly labels, categorical keys, weekday names.
Normalize where needed (split, trim, clean, lower/upper case).
Conform types (whole number, decimal, date/time, text).
Document steps (rename steps, add brief notes in query descriptions).
Load to the model, build relationships, then DAX and visuals.

You’ll be amazed how much ground you can cover with Column from Examples—it’s a force multiplier for simple data preparation for Power BI using Power Query.

Common Pitfalls (and How to Avoid Them)

Inconsistent input formatting
If some rows use Last, First while others use First Last, give examples for both patterns so the inference can reconcile them—or normalize the inputs first.
Hidden whitespace
Invisible leading/trailing spaces can skew results. Use Transform ► Format ► Trim/Clean, or add an example that clearly excludes them.
Nulls vs blanks
Null values can throw off concatenation unless removed or replaced. Consider List.RemoveNulls or Text.Combine with pre‑cleaned inputs.
Locale surprises
Date names, decimal separators, and currency symbols vary. Specify the intended locale in your dataflow or document it in the solution notes.
Step order
Type conversions should happen at the right time (e.g., parse dates before extracting weekday). Reorder steps when needed.

FAQs: Simple Data Preparation for Power BI Using Power Query

Q1: Is Column from Examples using AI?
It’s a smart pattern recognition feature that infers transformations from your examples—similar in spirit to Excel’s Flash Fill. Whether you call it “AI” or not, its goal is the same: cut the time from intention to M code.

Q2: Will Column from Examples slow down my refresh?
No. Once accepted, it becomes normal M code. There is no special runtime penalty compared to writing the same step manually.

Q3: Should I use “From All Columns” or “From Selection”?
If your output depends on multiple fields, choose From All Columns. If it depends on one or two known fields, From Selection is faster—especially in wide tables.

Q4: Can I use this in Dataflows and Excel?
Yes. You’ll find a comparable experience in Power Query Online (Dataflows Gen1/Gen2) and Power Query in Excel.

Q5: The inference guessed wrong—now what?
Provide a second (or third) example that clarifies the pattern. You can also switch to the formula bar and fine‑tune the generated M.

Q6: Is there any reason to learn M if Column from Examples exists?
Column from Examples accelerates you, but knowing M unlocks deeper control, performance tuning, and maintainability. The best part: reading the generated code is a gentle way to learn M over time.

A Quick Checklist Before You Click “Close & Apply”

I used Column from Examples for repetitive text/date derivations.
I verified the inferred logic with at least two examples for ambiguous patterns.
I renamed steps descriptively (e.g., Added Full Name, Added Birth Weekday).
I checked types (text/date/number) and trimmed/cleaned text where needed.
I validated a few edge rows and null scenarios.
I confirmed the step order makes sense (type conversions before derivations, etc.).

That’s simple data preparation for Power BI using Power Query—done right, done fast, and done in a way your colleagues can understand.

Final Thoughts

I’ve been building and teaching Power BI solutions for years, and I still reach for Column from Examples on day‑one data shaping. It’s the perfect bridge between what you want and how to express it in M. The more you use it, the more you’ll internalize the building blocks of Power Query—and the faster your whole team will ship reliable, model‑ready data.

If you found this guide helpful, share it with your team and keep it handy for your next project. And if you’d like me to write up additional patterns—or want a deep‑dive on optimizing refresh performance—let me know. I create weekly content on Power BI and Microsoft Fabric, and I love turning real‑world questions into practical walkthroughs.