PQ can a column's data type be referenced in a formula?

[cr731]

In Power Query, is it possible to refer to a column's data type? I would like to create a function to trim all columns whose data type is text or any dynamically.

 

[anvg]

Hi
This post is about how to get column data type http://community.powerbi.com/t5/Des...mn-in-Power-Query-Formula-Language/td-p/20518
Regards

 

[MarcelBeug]

Actually, Table.Schema is just a friendly way to get column data types.
The core function is Type.TableColumn.

This formula will give you a table with column names and their types of table Source:

= Table.FromRows(List.Transform(Table.ColumnNames(Source), each {_, Type.TableColumn(Value.Type(Source),_)}),type table[ColumnName = text,ColumnType = type])

Otherwise the column types don't guarantee that the actual values in the column are of the same type.
With type any, you can have any type (so Text.Trim might result in error).

You might consider trimming all actual text values in your entire table, like in:

= Table.TransformColumns(Source,{}, each if _ is text then Text.Trim(_) else _)

 

[anvg]

Actually, Table.Schema is just a friendly way to get column data types.

With that function is simpler to select text type columns for trimming.

= Table.TransformColumns(Source, List.Transform(Table.SelectRows(Table.Schema(Source), each [Kind] = "text")[Name], each {_, (val) => Text.Trim(val), type text}))

Regards,

 

[cr731]

Thank you for the responses, this will save a lot of time when importing and choosing the columns to trim.

 

[cr731]

With that function is simpler to select text type columns for trimming.

= Table.TransformColumns(Source, List.Transform(Table.SelectRows(Table.Schema(Source), each [Kind] = "text")[Name], each {_, (val) => Text.Trim(val), type text}))

Regards,

This is a bit old but I was just using this again and wondering, what is the purpose of the red portion

= Table.TransformColumns(Source, List.Transform(Table.SelectRows(Table.Schema(Source), each [Kind] = "text")[Name], each {_, [COLOR=#FF0000][B](val) => Text.Trim(val), type text[/B][/COLOR]}))

I replaced it with the following and it seems like it worked the same, just wondering what if it is or I'm overlooking something

= Table.TransformColumns(Source, List.Transform(Table.SelectRows(Table.Schema(Source), each [Kind] = "text")[Name], each {_, [COLOR=#FF0000][B]Text.Trim[/B][/COLOR]}))

Thanks