Power QueryExercises

Combine text values and handle missing parts

Build combined text such as full names and labels without stray nulls or separators.

~8 min read · beginner · Module 6: Text transformations

By Tahir Nas · Examples run in our simulator · Updated

Before this: Trim, clean and capitalise text, null, empty text and errors are different things

Joining text looks trivial until a part is missing. The & operator and Text.Combine treat null very differently, and choosing the right one decides whether the result is a clean label or an empty cell.

& returns null when any part is null

let
    Source = #table({"First", "Middle", "Last"}, {{"Ana", null, "Ruiz"}, {"Ben", "K", "Okafor"}}),
    WithAmp = Table.AddColumn(Source, "With &", each [First] & " " & [Middle] & " " & [Last], type text),
    WithCombine = Table.AddColumn(WithAmp, "With Text.Combine", each Text.Combine({[First], [Middle], [Last]}, " "), type text)
in
    Table.SelectColumns(WithCombine, {"With &", "With Text.Combine"})

Result

With &With Text.Combine
nullAna Ruiz
Ben K OkaforBen K Okafor

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Text.Combine ignores null items, so no double space appears where the middle name is missing.

That's why a merged-names column built with & sometimes has blanks for exactly the customers with a missing middle name. Text.Combine is the safer default for labels.

Empty text isn't skipped

Text.Combine skips null, but not empty text. Text.Combine({"Ana", "", "Ruiz"}, " ") gives "Ana Ruiz" with two spaces. Standardise empty text to null first, or filter the parts:

Text.Combine(List.Select({"Ana", "", "Ruiz"}, each _ <> null and _ <> ""), " ")

Result

Ana Ruiz

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Numbers and dates need converting

Both & and Text.Combine work on text only. "Order " & 42 raises We cannot apply operator & to types Text and Number. Convert with Text.From, which also handles dates, or use Text.Format with placeholders:

let
    Source = #table({"OrderID", "Qty"}, {{"A-101", 2}, {"A-102", 15}}),
    Label = Table.AddColumn(Source, "Label", each Text.Format("#{0}: #{1} units", {[OrderID], Text.From([Qty])}), type text)
in
    Label

Result

OrderIDQtyLabel
A-1012A-101: 2 units
A-10215A-102: 15 units

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Both approaches handle text only, so a date part needs converting too. Text.From(#date(2026, 3, 14)) follows the culture's date format; for a stable label use Date.ToText(d, [Format = "yyyy-MM-dd"]), which reads the same on every machine.

Choosing a separator

  • Use a space or comma for display labels.
  • Use a character that never appears in the data (such as |) for technical keys.
  • Don't build keys from free text without trimming it first; invisible spaces make different keys.

Where to find this in Excel

Excel for Windows
Transform > Merge Columns, or Add Column > Custom Column with &
What you see
Merge Columns needs text columns. A custom column keeps the originals and lets you decide what a missing part becomes.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Merge columns (Power Query) (Microsoft Support).

Common mistakes

  • Using & with optional parts and getting null for the whole label.
  • Assuming Text.Combine skips empty text as well as null.
  • Concatenating a number without Text.From and getting an operator type error.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Add Column → Merge Columns uses a combiner; Add Column → Custom Column is where you write & or Text.Combine yourself.
  • Excel's worksheet & treats an empty cell as empty text, so formula users expect "a" & blank to be "a". In M, "a" & null is null.
  • The simulator's Text.Combine skips null items as documented, and Text.Format supports #{n} and #[name] placeholders.
Functions in this lesson:Text.CombineText.FromText.Format

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Text transformations quiz