CODEWITHSUNDEEP
functiontypespowerbimetadatapowerquery
Denis
Denis

Reputation: 781

PowerQuery: How to get Column type?

How to get type for specified ColumnName in Table?

Example: How to get type text for column FirstName: enter image description here

Table code:

= Table.FromRows(
        {
            {1, "Bob", "Smith", "123-4567"},
            {2, "Jim", "Brown", "987-6543"},
            {3, "Paul", "Wick", "543-7890"}
        },
        {"CustomerID", "FirstName", "LastName", "Phone"}    
    )

P.S. Solution as function strongly appreciated! :)

Related Topics:

  1. PowerQuery: How to get Column type?
  2. Powerquery: how to convert/cast type Type to type Text?
  3. PowerQuery: type definition, difference between Table.Scema fields [TypeName] and [Kind]

Upvotes: 2

Views: 3493

Answers (3)

SSlinky
SSlinky

Reputation: 488

Here's how I get a table type for the passed in table.

GetTableType = (tbl as table) as type =>
let
    colNames = Table.ColumnNames(tbl),
    colTypes = List.Transform(
        colNames,
        (x) => [Type=Value.Type(Table.Column(tbl, x)), Optional=false]
    )
in
    type table Type.ForRecord(
        Record.FromList(
            colTypes,
            colNames
        ),
        false
    )

Then you can use it to simplify grouping rows.

Table.Group(
    Source,
    listOfCols,
    {{"Rows", each _, GetTableType(Source)}}
)

Upvotes: 0

Denis
Denis

Reputation: 781

Use combination of Type.TableColumn and Value.Type functions:

Type.TableColumn(Value.Type(TableName),ColumnName)

Upvotes: 3

Denis
Denis

Reputation: 781

Please try this function sfnClmType (download):

(TableName as table, ColumnName as text)    => 
let valTypeTxt = Table.SelectRows(Table.Schema(TableName), each [Name] = ColumnName){0}[Kind]
in  Expression.Evaluate("type "& valTypeTxt)

enter image description here

Connected topics:

  1. How to convert/cast type Type to type Text

Upvotes: 1

Related Questions