excel - How to refer columns in Power Query by index or position?

Question

I have a line as below in Power Query. So instead of referring it by name, I want it by position dynamically. Can someone help here, please

#"Filtered Part Desc" = Table.SelectRows(#"Removed Columns3", each
                            Text.Contains([part_desc], "ENG") or
                            Text.Contains([part_desc], "TRANS"))

I tried replacing [part_desc] by Table.ColumnNames(Promoted){6}, but it is not working

Answer 1

You can use Table.ColumnNames(MyTable){n} to return a column name by its position - this this is base 0, so the 6th column name would be Table.ColumnNames(MyTable){5}

You can then use Record.Field to reference a column by its name.

You can also filter by a list, rather than stringing criteria together with the or operator.

So, putting this together for your example:

    #"Filtered Part Desc" = Table.SelectRows ( 
        #"Removed Columns3", 
        each List.Contains(
            {"ENG","TRANS"}, 
            Record.Field(_, Table.ColumnNames(#"Removed Columns3"){5})
        )
    )