How to select column values based on the column position in a table through Power Query ?

[Anonymous]

Hi all,

probably this is going to be an easy one but for some reason I am stuck here.

I have a table in Power BI Desktop and am trying to add a Custom Column in the Query Editor. The table is called "Product". What I am trying to do is to select column values based on the position of the column in that table. I found a similar discussion that I used for orientation but could not get it to work (https://community.powerbi.com/t5/Desktop/How-to-select-column-based-on-their-positions-in-a-table-th...).

If the table looks like that, I would, for example, want to create an additional column populated with the values in the 3rd column.

Base table:

Column1	Column2	Column3
A	AA	AAA
B	BB	BBB
C	CC	CCC

New table with Custom Column:

Column1	Column2	Column3	New Column
A	AA	AAA	AAA
B	BB	BBB	BBB
C	CC	CCC	CCC

My code to select the values from the 3rd column in the table:

New Column = Table.AddColumn(#"Changed Type2", "New Column", each Table.SelectColumns(#"Product",{Table.ColumnNames(#"Product"){2}}))

The code throws the error: Expression.Error: A cyclic reference was encountered during evaluation.

In a next step, I want to select the column dynamically by using the value in another column for the respective row. I assume, this would be simply achieved by replacing the {2} above with {[Column Value]}.

Thank you for your help!

[PaulDBrown]

Try:

= Table.AddColumn(Source, "Merged", each Text.Combine({[Column1], [Column2]}), type text)

[Anonymous]

Hi Paul,

thanks for the reply. I am not trying to combine the content of the columns - sorry if that was unclear. I am trying to use the values from a single column, in the example above column 3, to populate a new column. I want to select this column based on its position {2} in the table.