Splitter.SplitTextByDelimiter
Returns a splitter that cuts text into pieces wherever a chosen delimiter appears, such as a comma, a tab or a semicolon.
Splitter.SplitTextByDelimiter(delimiter, optional quoteStyle)Returns: A function. You pass it to a step such as Table.SplitColumn or List.Transform, and it gives back a list of pieces.
What it does
A splitter is a rule for cutting text. Splitter.SplitTextByDelimiter builds a rule that cuts at one delimiter. Use it to split a column such as "Account,Name,Amount" into separate columns, or a text field where pieces are separated by a semicolon. You pass the splitter to a step that does the cutting, most often Table.SplitColumn.
Syntax and arguments
delimiter: the text that marks the cut, such as",",";"or"#(tab)"for a tab character.quoteStyle(optional): how quoted text is treated.QuoteStyle.Csvrespects double quotes, so a comma inside quotes does not cut the text.QuoteStyle.Nonetreats every delimiter as a cut.
Example
A bank export puts the payee, the reference and the amount in one column, separated by semicolons. The reviewer wants three columns.
let
Source = Table.FromRecords({
[Line = "Acme Supply;INV-4471;1250.00"],
[Line = "Bolt Hardware;INV-4472;480.00"]
}),
Split = Table.SplitColumn(Source, "Line",
Splitter.SplitTextByDelimiter(";", QuoteStyle.None),
{"Payee", "Reference", "Amount"})
in
Split
Split has three columns, and each row holds the payee, the invoice reference and the amount as text. Convert the amount to a number before you total it.
Example: a second, different use
Splitting one text value into a list: Splitter.SplitTextByDelimiter(",")("Q1,Q2,Q3") returns {"Q1", "Q2", "Q3"}. The second pair of brackets calls the splitter on the text.
Things to watch
- Use
QuoteStyle.Csvwhen a field can contain a quoted comma, such as a company name like "Smith, Jones & Co". - The pieces are text. Convert the amounts and dates after splitting.
- Count the pieces. A payee that contains the delimiter gives an extra piece, which can break the column names you set. Check a sample of rows after each refresh.
- For text in a file, Csv.Document is usually better than splitting by hand.
In business terms
Pulling fields out of a single text column is common with older system exports, memo fields and mainframe reports. A visible split step is easy for a reviewer to test against a few sample rows.