Прочетете на английски

Споделяне чрез

Table.TransformColumnTypes

  • Статия

Syntax

Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table

About

Returns a table by applying the transform operations to the specified columns using an optional culture.

  • table: The input table to transform.
  • typeTransformations: The type transformations to apply. The format for a single transformation is { column name, type value }. A list of transformations can be used to change the types of more than one column at a time. If a column doesn't exist, an error is raised.
  • culture: (Optional) The culture to use when transforming the column types (for example, "en-US").

The type value in the typeTransformations parameter can be any, all of the number types, text, all of the date, time, datetime, datetimezone, and duration types, logical, or binary. The list, record, table, or function types aren't valid for this parameter.

Example 1

Transform the number values in the first column to text values.

Usage

Power Query M 
let
    Source = #table(type table [a = number, b = number],
    {
        {1, 2},
        {3, 4}
    }),
    #"Transform Column" = Table.TransformColumnTypes(
        Source, 
        {"a", type text}
    )
in
    #"Transform Column"

Output

Power Query M 
#table(type table [a = text, b = number],
{
    {"1", 2},
    {"3", 4}
})

Example 2

Transform the dates in the table to their French text equivalents.

Usage

Power Query M 
let
    Source = #table(type table [Company ID = text, Country = text, Date = date],
    {
        {"JS-464", "USA", #date(2024, 3, 24)},
        {"LT-331", "France", #date(2024, 10, 5)},
        {"XE-100", "USA", #date(2024, 5, 21)},
        {"RT-430", "Germany", #date(2024, 1,18)},
        {"LS-005", "France", #date(2023, 12, 31)},
        {"UW-220", "Germany", #date(2024, 2, 25)}
    }),
    #"Transform Column" = Table.TransformColumnTypes(
        Source,
        {"Date", type text},
        "fr-FR"
    )
in
    #"Transform Column"

Output

Power Query M 
#table(type table [Company ID = text, Country = text, Date = text],
    {
        {"JS-464", "USA", "24/03/2024"},
        {"LT-331", "France", "05/10/2024"},
        {"XE-100", "USA", "21/05/2024"},
        {"RT-430", "Germany", "18/01/2024"},
        {"LS-005", "France", "31/12/2023"},
        {"UW-220", "Germany", "25/02/2024"}
    })

Example 3

Transform the dates in the table to their German text equivalents, and the values in the table to percentages.

Usage

Power Query M 
let
    Source = #table(type table [Date = date, Customer ID = text, Value = number],
    {
        {#date(2024, 3, 12), "134282", .24368},
        {#date(2024, 5, 30), "44343", .03556},
        {#date(2023, 12, 14), "22", .3834}
    }),
    #"Transform Columns" = Table.TransformColumnTypes(
        Source, 
        {{"Date", type text}, {"Value", Percentage.Type}},
        "de-DE") 
in
    #"Transform Columns"

Output

Power Query M 
#table(type table [Date = text, Customer ID = text, Value = Percentage.Type],
{
    {"12.03.2024", "134282", .24368},
    {"30.05.2024", "44343", .03556},
    {"14.12.2023", "22", .3834}
})

Related content