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
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"]
} )
*/
Related functions
Other functions related to Table.SplitColumn are:
- Table.CombineColumns
- Table.CombineColumnsToRecord
- Table.PrefixColumns
- Table.TransformColumnTypes
- Table.TransformColumns
- Table.TransformRows
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy