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 |
|---|---|
| null | Ana Ruiz |
| Ben K Okafor | Ben K Okafor |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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
LabelResult
| OrderID | Qty | Label |
|---|---|---|
| A-101 | 2 | A-101: 2 units |
| A-102 | 15 | A-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" & blankto be "a". In M,"a" & nullis null. - The simulator's Text.Combine skips null items as documented, and Text.Format supports #{n} and #[name] placeholders.
Text.CombineText.FromText.FormatNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Text transformations quiz