Text.Combine
Joins a list of text values into one text value, with a separator between them.
Text.Combine(texts as list, optional separator as nullable text) as textReturns: One text value.
What it does
Text.Combine is the reverse of Text.Split. It takes a list of text values and joins them into one string. Finance teams use it to build a readable list of invoice numbers for a memo or an email, or to rebuild a key from parts.
Syntax and arguments
- texts: a list of text values. Numbers in the list must be converted to text first.
- separator (optional): the text placed between items. Leave it out and the items are joined with nothing between them.
Example
A collections report needs one cell that lists every open invoice for a customer, so the collector can paste it into a reminder email.
let
Open = #table(type table [Customer = text, Invoice = text],
{{"Acme Supply Co", "INV1043"}, {"Acme Supply Co", "INV1044"}, {"Blue Harbor LLC", "INV1051"}}),
Grouped = Table.Group(Open, {"Customer"}, {
{"Invoices", each Text.Combine([Invoice], ", "), type text}
})
in
Grouped
Acme Supply Co gets "INV1043, INV1044" and Blue Harbor LLC gets "INV1051". Table.Group collects the invoices for each customer into a list, and Text.Combine joins them.
Example: a second, different use
Join the parts of a key, such as a company code, a year and a period, into one text value with a dash between each part.
Things to watch
- Every item must be text. A number inside the list causes an error, so convert it with Text.From.
- A null inside the list can cause an error or an empty piece, so clean the list first with List.RemoveNulls if the data can have blanks.
- The separator is placed only between items, not at the start or end.
- Keep the list short when you build a text for a cell. Very long text is hard to read in a report.
In business terms
Collections reminders, audit requests and remittance notes often need a list of references in one field. Text.Combine builds that list from the detail rows without someone retyping them.