Forum Discussion
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:
| 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!
2 Replies
- PaulDBrown
Community Champion
Try:
= Table.AddColumn(Source, "Merged", each Text.Combine({[Column1], [Column2]}), type text)- AnonymousNot 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.