Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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-through/td-p/2483259).

 

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:

Column1Column2Column3
AAAAAA
BBBBBB
CCCCCC

 

New table with Custom Column:

Column1Column2Column3New Column
AAAAAAAAA
BBBBBBBBB
CCCCCCCCC

 

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!

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion

    Try:

     

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

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      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.