How to look up a value by column name - HALP

[BlueCatOwner01]

Hi all, 

I am trying to create a new column which would look up the value in another column in the same row based on the value in another column where each row is a column name. 

I know sounds confusing right? Hope this visual helps

Column A	Column B	Column C	Column D	Column E	Column F	Column Names	Value Look Up
1	1	1	1	1	1	Column A
2	2	2	2	2	2	Column C
3	3	3	3	3	3	Column F
4	4	4	4	4	4	Column D
5	5	5	5	5	5	Column B

I want to populate values in the "Value Look Up" Column by looking up the value in the same row based on the column name value found in the "Column Names" Column.

The current DAX code I am trying to write is something along the lines of:

Value Look Up = 'Table1'['Table1'[Column Names]]

But as you can imagine, Power BI is not having it.

Any help would be apprecaited.

[Ashish_Mathur · Accepted solution]

Hi,

This would be easiest to solve with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", Int64.Type}, {"Column B", Int64.Type}, {"Column C", Int64.Type}, {"Column D", Int64.Type}, {"Column E", Int64.Type}, {"Column F", Int64.Type}, {"Column Names", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each Record.ToList(_){List.PositionOf(Table.ColumnNames(#"Changed Type"),[Column Names])})
in
    #"Added Custom1"

Hope this helps.

[mostho]

you could use the DAX-Function SWITCH

[BlueCatOwner01]

Could you give me an example please?

[Irwan]

hello @BlueCatOwner01 

using SWITCH to get this result

Value Look Up =
SWITCH(
    TRUE(),
    'Table'[Column Names]="Column A",'Table'[Column A],
    'Table'[Column Names]="Column B",'Table'[Column B],
    'Table'[Column Names]="Column C",'Table'[Column C],
    'Table'[Column Names]="Column D",'Table'[Column D],
    'Table'[Column Names]="Column E",'Table'[Column E],
    'Table'[Column Names]="Column F",'Table'[Column F]
)

However, the problem is when you have a lot of columns, then you need to type each of them.

If you have a lot of columns, maybe you might want to unpivot them first to process the data.
Unpivot is for combining those columns into one column (processing one column is much easier than multiple column), but thats depend on your goal.

Hope this will help.

Thank you.