Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 483
Sam K Avatar asked May 01 '18 08:05

Sam K


People also ask

How do you refer to a column in Power Query?

Look at the formula bar (click View -> Formula Bar if it is not visible), it contains the syntax for referencing a specific row and column within Power Query. {0} references the first row of data. In Power Query, counting always starts at zero. [Profit] is the name of the column.

What is an index column in Power Query?

The Index column command adds a new column to the table with explicit position values, and is usually created to support other transformation patterns. By default, the starting index will start from the value 0 and have an increment of 1 per row.


1 Answers

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})
        )
    )
like image 125
Olly Avatar answered Sep 19 '22 15:09

Olly