Referring to columns by position rather than name

[omezquita]

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}}),[FONT=arial, sans-serif] [/FONT]

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}}),[FONT=arial, sans-serif] [/FONT]

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}}),[FONT=arial, sans-serif] [/FONT]

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

Thanks!

 

[bdarbonneau]

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]

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"