跳至主要内容

Table.SplitColumn

使用指定的分隔器函數，將指定的資料行分割成一組額外的資料行。

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

Remarks​

使用指定的分隔器函數，將指定的資料行分割成一組額外的資料行。

  • table: 包含要分割之資料行的表格。
  • sourceColumn: 要分割的資料行的名稱。
  • splitter: 用來分割資料行的分割器函數 (例如 Splitter.SplitTextByDelimiter 或 Splitter.SplitTextByPosition)。
  • columnNamesOrNumber: 要建立的新資料行名稱清單，或新資料行數目。
  • default: 當分割值不足，無法填滿所有新資料行時，會覆寫使用的值。此參數的預設值是 null。
  • extraColumns: 指定如果分割值的數量超過新資料行數目時，該執行的動作。您可以將 ExtraValues.Type 列舉值傳遞給此參數。預設值是 ExtraValues.Ignore。

Examples​

Example #1​

將名稱資料行分割成名字和姓氏。

let
    Source = #table(type table[CustomerID = number, Name = text, Phone = text],
    {
        {1, "Bob White", "123-4567"},
        {2, "Jim Smith", "987-6543"},
        {3, "Paul", "543-7890"},
        {4, "Cristina Best", "232-1550"}
    }),
    SplitColumns = Table.SplitColumn(
        Source,
        "Name",
        Splitter.SplitTextByDelimiter(" "))
in
    SplitColumns

Result:

#table(type table[CustomerID = number, Name.1 = text, Name.2 = text, Phone = text],
{
    {1, "Bob", "White", "123-4567"},
    {2, "Jim", "Smith", "987-6543"},
    {3, "Paul", null, "543-7890"},
    {4, "Cristina", "Best", "232-1550"}
})

Example #2​

將名稱資料行分割為名字和姓氏，然後重新命名新資料行。

let
    Source = #table(type table[CustomerID = number, Name = text, Phone = text],
    {
        {1, "Bob White", "123-4567"},
        {2, "Jim Smith", "987-6543"},
        {3, "Paul", "543-7890"},
        {4, "Cristina Best", "232-1550"}
    }),
    SplitColumns = Table.SplitColumn(
        Source,
        "Name",
        Splitter.SplitTextByDelimiter(" "),
        {"First Name", "Last Name"})
in
    SplitColumns

Result:

#table(type table[CustomerID = number, First Name = text, Last Name = text, Phone = text],
{
    {1, "Bob", "White", "123-4567"},
    {2, "Jim", "Smith", "987-6543"},
    {3, "Paul", null, "543-7890"},
    {4, "Cristina", "Best", "232-1550"}
})

Example #3​

將名稱資料行分割為名字和姓氏、重新命名新資料行，並以 “-No Entry-” 填入任何空白。

let
    Source = #table(type table[CustomerID = number, Name = text, Phone = text],
    {
        {1, "Bob White", "123-4567"},
        {2, "Jim Smith", "987-6543"},
        {3, "Paul", "543-7890"},
        {4, "Cristina Best", "232-1550"}
    }),
    SplitColumns = Table.SplitColumn(
        Source,
        "Name",
        Splitter.SplitTextByDelimiter(" "),
        {"First Name", "Last Name"},
        "-No Entry-")
in
    SplitColumns

Result:

#table(type table[CustomerID = number, First Name = text, Last Name = text, Phone = text],
{
    {1, "Bob", "White", "123-4567"},
    {2, "Jim", "Smith", "987-6543"},
    {3, "Paul", "-No Entry-", "543-7890"},
    {4, "Cristina", "Best", "232-1550"}
})

Example #4​

將名稱資料行分割為名字和姓氏，然後重新命名新資料行。由於值的數量可能多於可用的資料行數目，所有請將姓氏資料行設為清單，包含名字之後的所有值。

let
    Source = #table(type table[CustomerID = number, Name = text, Phone = text],
    {
        {1, "Bob White", "123-4567"},
        {2, "Jim Smith", "987-6543"},
        {3, "Paul Green", "543-7890"},
        {4, "Cristina J. Best", "232-1550"}
    }),
    SplitColumns = Table.SplitColumn(
        Source,
        "Name",
        Splitter.SplitTextByDelimiter(" "),
        {"First Name", "Last Name"},
        null,
        ExtraValues.List)
in
    SplitColumns

Result:

#table(type table[CustomerID = number, First Name = text, Last Name = text, Phone = text],
{
    {1, "Bob", {"White"}, "123-4567"},
    {2, "Jim", {"Smith"}, "987-6543"},
    {3, "Paul", {"Green"}, "543-7890"},
    {4, "Cristina", {"J.", "Best"}, "232-1550"}
})

Category​

Table.Transformation