Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Birinder
Helper III
Helper III

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

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.

2 ACCEPTED SOLUTIONS
AntonioM
Solution Sage
Solution Sage

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

View solution in original post

Vijay_A_Verma
Super User
Super User

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

View solution in original post

3 REPLIES 3
Rickmaurinus
Helper V
Helper V

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
Super User
Super User

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
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.