PDA

View Full Version : Referring to columns by position rather than name



omezquita
2016-10-23, 02:21 PM
Hi,

I have the following line of code that assigns the data type for each column.


#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Jan 2016", Int64.Type}, {"Feb 2016", Int64.Type}, {"Mar 2016", Int64.Type}}),

I would like to convert the query to a function and apply it to different tables that will have the same number of columns but different column names. Is there a way to refer to the columns by their position rather than their name?

Something like:


#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {2, Int64.Type}, {3, Int64.Type}, {4, Int64.Type}}),

I know that I could demote the headers and then do something like:


#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),

But I would like to know if there's a way without the additional step.

Thanks!

bdarbonneau
2016-10-24, 08:57 AM
You can use the function "Table.ColumnNames" wich returns a list containing the names of columns from a table, then use the notation {n} to return the nth item in the list.
Ex: Table.ColumnNames(Souce){1} equals "Jan 2016"


Hi,

I have the following line of code that assigns the data type for each column.


#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Jan 2016", Int64.Type}, {"Feb 2016", Int64.Type}, {"Mar 2016", Int64.Type}}),

I would like to convert the query to a function and apply it to different tables that will have the same number of columns but different column names. Is there a way to refer to the columns by their position rather than their name?

Something like:


#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {2, Int64.Type}, {3, Int64.Type}, {4, Int64.Type}}),

I know that I could demote the headers and then do something like:


#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),

But I would like to know if there's a way without the additional step.

Thanks!

bdarbonneau
2016-10-24, 09:27 AM
An you can take it one step furter: generate with Power Query the format transformations:

Transforms= List.Combine({{{Table.ColumnNames(Source){0}, type text}},List.Transform( List.Skip(Table.ColumnNames(Source)),each {_,Int64.Type})}),
#"Changed Type" = Table.TransformColumnTypes(Source,Transforms)




You can use the function "Table.ColumnNames" wich returns a list containing the names of columns from a table, then use the notation {n} to return the nth item in the list.
Ex: Table.ColumnNames(Souce){1} equals "Jan 2016"