Table.SplitColumn - Function

Table.SplitColumn is a Power Query M function that splits specified columns in a table into a set of additional columns using a splitter function. The function returns a table with the additional columns.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.SplitColumn(
   table as table,
   sourceColumn as text,
   splitter as function,
   optional columnNamesOrNumber as any,
   optional default as any,
   optional extraColumns as any,
) as table

Argument	Attribute	Description
Table		The list of values to create a table from.
SourceColumn		The column to split.
Splitter		The splitter function to apply to the values in the list.
ColumnNames Or Number	Optional	Specifies the column. Can be provided as number of columns, list of columns or as TableType.
Default	Optional	When omitting this argument, missing values show as null. Alternatively, you can provide a default value to show instead of null.
ExtraColumns	Optional	Provide the ExtraValues.Type to specify the expected action for extra values in a row that has less columns than expected. You can choose from ExtraValues.Error, ExtraValues.Ignore or ExtraValues.List.

Description

Splits the specified columns into a set of additional columns using the specified splitter function.

Examples

Split the [Name] column at position of “i” into two columns

let
    Customers = Table.FromRecords( {
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Cristina", Phone = "232-1550"]
    } )
in
    Table.SplitColumn( Customers, "Name", Splitter.SplitTextByDelimiter( "i" ), 2 )

 /* Output: 
Table.FromRecords( {
    [CustomerID = 1, Name.1 = "Bob", Name.2 = null, Phone = "123-4567"],
    [CustomerID = 2, Name.1 = "J", Name.2 = "m", Phone = "987-6543"],
    [CustomerID = 3, Name.1 = "Paul", Name.2 = null, Phone = "543-7890"],
    [CustomerID = 4, Name.1 = "Cr", Name.2 = "st", Phone = "232-1550"]
} )
 */

Other functions related to Table.SplitColumn are:

Last update: August 28, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-splitcolumn
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.

Syntax

Description

Examples

Related functions