How to select column based on their positions in a table through Power Query ?

[Birinder]

Hi there,
I know how to get a single column by its position in a table:

= Table.SelectColumns(#"Promoted Headers",{Table.ColumnNames(#"Promoted Headers"){1}})

However, I am little helpless in the case where I don't know how to do the same for selecting more than one column.
Suppose I want to select columns from position 0 till position 5, How can I do that ?

Thanks and regards.

[AntonioM · Accepted solution]

It's a little awkward but one thing you could do is 

= Table.SelectColumns(#"Promoted Headers",{Table.ColumnNames(#"Promoted Headers"){1}, Table.ColumnNames(#"Promoted Headers"){2}, Table.ColumnNames(#"Promoted Headers"){3}})

to get 1, 2 and 3

[Rickmaurinus]

The general pattern you can use is: 

let
  myColumns = List.Transform( { 0, 2 },  
                              each Table.ColumnNames( Source ) { _ } ),
  selectrows = Table.SelectRows( Source, myColumns )
in
  selectrows 

It generates a list of the positions you want to retrieve, in this case, 0 and 2. For each position, it will look for the column names in your step and retrieve an item. It then returns a list with the resulting items.  

You can see some more examples and the final function details right here: 

https://gorilla.bi/power-query/select-items-by-position-in-list/

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[Vijay_A_Verma · Accepted solution]

You will need to use List.Range property for this. Look below to see the columns from 0 till position 5

= Table.SelectColumns(#"Promoted Headers",List.Range(Table.ColumnNames(#"Promoted Headers"),0,6))

0 - Starting index is 0

6 - Total 6 columns i.e. position 5

[AntonioM · Accepted solution]

It's a little awkward but one thing you could do is 

= Table.SelectColumns(#"Promoted Headers",{Table.ColumnNames(#"Promoted Headers"){1}, Table.ColumnNames(#"Promoted Headers"){2}, Table.ColumnNames(#"Promoted Headers"){3}})

to get 1, 2 and 3