Select columns with specified data type

[monojchakrab]

I have a table with 50+ columns...

I want to select only the columns which have numeric data type.

Is it possible to use some combination of Table.ColumnNames or Table.SelectColumns with Value.Type to achieve this?

Many thanks in advance

[artpil · Accepted solution]

Hi,

If you want to select columns using columns' defined type then you could use code like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVNJRMgRiAz0waa5vYKZvZGBkpBSrA5M2AksbQaXNodKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [text = _t, int = _t, decimal = _t, date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"date", type date}, {"decimal", type number}, {"int", Int64.Type}, {"text", type text}}, "en-US"),
    TableSchemaCall = Table.Schema(ChangedType),
    FilteredRows = Table.SelectRows(TableSchemaCall, each ([TypeName] = "Number.Type")),
    ToList = FilteredRows[Name],
    SelectionOfColumns = Table.SelectColumns( #"ChangedType",ToList)
in
    SelectionOfColumns

Artur

[Vijay_A_Verma · Accepted solution]

If you see my code, second argument was {type nullable number} and you had put {type number}. Unless you use nullable keyword, it will not give you the result.

If you need name of the columns only, then

= Table.ColumnsOfType(#"Filtered Rows",{type number})

[Vijay_A_Verma]

Table.ColumnsOfType is used for this purpose. Use following to select the columns where #"Changed Type" is previous step

= Table.SelectColumns(#"Changed Type",Table.ColumnsOfType(#"Changed Type", {type nullable number}))

[monojchakrab]

Hey @Vijay_A_Verma ,

I tried this method and I got the following as a result :

There are two problems with this :

1. It has selected all columns irrespective of the type; here's the code I used :

= Table.SelectColumns(#"Filtered Rows",Table.ColumnsOfType(#"Filtered Rows",{type number}))

2. It is not giving me the names of the columns - I would need to isolate the names of the columns which are of numeric data type

But appreciate the help

Best regds.,

[Vijay_A_Verma · Accepted solution]

If you see my code, second argument was {type nullable number} and you had put {type number}. Unless you use nullable keyword, it will not give you the result.

If you need name of the columns only, then

= Table.ColumnsOfType(#"Filtered Rows",{type number})

[monojchakrab]

Thanks @Vijay_A_Verma ...the code now works and returns the table only with numeric data types.

But when I tried the code for only column names with numeric data type as below :

Table.ColumnsOfType(#"Filtered Rows",{type number})

It is returning just an empty list with nothing in it :

Would really appreciate any help with this one - this will really help in the subsequent processes with this file.

Thanks and appreciate

[monojchakrab]

The Table.ColumnsOfType...works with {type nullable number} but not with {type number}...

Any reason for that and what is the difference by the way between the two?

Thanks

[artpil · Accepted solution]

If you would like to only select the columns with the type of percentage you can't use Table.ColumsOfType function because it treats all number kinds (whole numbers, decimals, percentage, currency) as numbers. IF for example if you want to select only currencies, you would have to Table.ScemaFunction.

Artur

[artpil]

Ho Vijay_A_Verma,

With this method you can't select only Int64.Type or only Number.Type or only Percentage.Type or only Currency.Type. Am I right?

Artur

[Vijay_A_Verma]

You are right.

This method would work only for primitive data types. Primitive data type "number" here would mean following

- Whole number (i.e. Integer)

- Decimal number

- Currency

- Percent

Hence, if you use Percentage.Type, Int64.Type, Number.Type, Currency.Type then the result would be same as that of type number.

Hence, using Int64.Type here woudn't retrieve only Whole number columns but all columns of type number.

Hence, if strictly any particular type is needed, your method is something which a person should go for.

[artpil · Accepted solution]

Hi,

If you want to select columns using columns' defined type then you could use code like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVNJRMgRiAz0waa5vYKZvZGBkpBSrA5M2AksbQaXNodKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [text = _t, int = _t, decimal = _t, date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"date", type date}, {"decimal", type number}, {"int", Int64.Type}, {"text", type text}}, "en-US"),
    TableSchemaCall = Table.Schema(ChangedType),
    FilteredRows = Table.SelectRows(TableSchemaCall, each ([TypeName] = "Number.Type")),
    ToList = FilteredRows[Name],
    SelectionOfColumns = Table.SelectColumns( #"ChangedType",ToList)
in
    SelectionOfColumns

Artur