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

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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:
Code:
= 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:
Code:
= Table.TransformColumns(Source,{}, each if _ is text then Text.Trim(_) else _)
 
Upvote 0
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.
Code:
= Table.TransformColumns(Source, List.Transform(Table.SelectRows(Table.Schema(Source), each [Kind] = "text")[Name], each {_, (val) => Text.Trim(val), type text}))
Regards,
 
Upvote 0
Thank you for the responses, this will save a lot of time when importing and choosing the columns to trim.
 
Upvote 0
With that function is simpler to select text type columns for trimming.
Code:
= 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

Code:
= 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

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

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top