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

Can you explain the logic of what you are trying to do? I don't understand what your intended goal is. — Alexis Olson
– Alexis Olson, Commented May 1, 2018 at 14:37

Olly · Accepted Answer · 2018-08-22 19:35:28Z

25

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})
        )
    )

edited Aug 22, 2018 at 19:35

answered May 2, 2018 at 8:28

Olly

7,9021 gold badge23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related