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


   table as table,
   sourceColumn as text,
   splitter as function,
   optional columnNamesOrNumber as any,
   optional default as any,
   optional extraColumns as any,
) as table
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.


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


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

    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"]
    } )
    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:

BI Gorilla Youtube Channel

Last update: August 28, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation:
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.