Downloads
Power Query MTextIntermediate

Text.Split

Splits a text value into a list of pieces at each separator.

Syntax
Text.Split(text as nullable text, separator as text) as list

Returns: A list of text values, one for each piece.

What it does

Text.Split breaks one value into a list at a separator you name. A remittance advice that lists several invoices in one field, such as "INV1043|INV1044|INV1047", becomes three invoice numbers that can be matched one at a time. The result is a list, not a table, so a step is usually needed to turn it into rows.

Syntax and arguments

Example

A customer paid three invoices with one wire, and the remittance field lists them separated by a vertical bar. The team lists them one per row so each can be matched to an open invoice.

let
    Payments = #table(type table [PaymentId = text, Invoices = text],
        {{"P-551", "INV1043|INV1044|INV1047"}, {"P-552", "INV1051"}}),
    Split = Table.AddColumn(Payments, "InvoiceList", each Text.Split([Invoices], "|"), type list),
    Expanded = Table.ExpandListColumn(Split, "InvoiceList"),
    Rows = Table.RemoveColumns(Expanded, {"Invoices"})
in
    Rows

P-551 becomes three rows and P-552 stays one row, so the result has four rows, each with one invoice number in InvoiceList. The payment id repeats on each row so the team can see the full payment.

Example: a second, different use

Split a cost center path such as "Dept-100/Region-West" into its parts with "/" to get the department and the region separately.

Things to watch

In business terms

Remittances, batch headers and multi-line memos often pack several items into one field. Splitting that field into rows is how a team matches one customer payment against several open invoices.

Related functions

All Power Query M functions · View as Markdown · M basics