Table.SplitColumn

Updated on

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
ArgumentAttributeDescription
TableThe list of values to create a table from.
SourceColumnThe column to split.
SplitterThe splitter function to apply to the values in the list.
ColumnNames Or NumberOptionalSpecifies the column. Can be provided as number of columns, list of columns or as TableType.
DefaultOptionalWhen omitting this argument, missing values show as null. Alternatively, you can provide a default value to show instead of null.
ExtraColumnsOptionalProvide 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

Table.SplitColumn divides specified columns of a table into multiple new columns, using a 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:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-splitcolumn