Retrieve value from same row based on column name in column

[marcelmunk]

Hi,

Pretty basic question (I believe) as I'm a beginner in Power BI and still on my basics on DAX.

I have a Table which has the Product Name and many properties

I want to add a new column, 'Value', which contains the value of the porperty under 'Desired Property'

Desired Result:

I guess I can do that with an 'if' function (I've seen an example in the community very similiar, but which had 2 properties only, solved with an 'if' statement)

As I already have the column name I want to extract the value from, there must be an easier way than pile up 20 'if'.

Thanks

[AlexisOlson]

I'd recommend a much simpler and more efficient approach.

Full sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIvSk3NA9LBGYlFBUA6Aoid83Pyi5RidaKVnIA8p5zSVJCC3Pz8kgwgIxKIQ1IrSkqLUsFqnIH8oNQUkL7SkpLMvHQgKwqIfVJLSlKBxsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Name" = _t, Color = _t, Texture = _t, Letter = _t, #"Desired Property" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Value", each Record.Field(_, [Desired Property]))
in
    #"Added Custom"

[NickOP · Accepted solution]

Here's an alternative solution that reads the record itself:

= Table.AddColumn(Source, "Value", each Record.FieldOrDefault(_,[Desired Property], null))

[Vijay_A_Verma]

Use the below formula

= try Table.Column(Source,[Desired Property]){List.PositionOf(List.Select(Table.ColumnNames(Source),(x)=>Text.Start(x,4)="Prop"),[Desired Property])} otherwise null

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIvSk3NA9LBGYlFBUDaFYiNTYBEBBAHFOUXGAFppVidaCUnIMMppzQVpDg3P78kA8jwBWIzcyARCVVtCFPtDGQEpaYASefSkpLMvHQgywOILSyBRBRUtSlYdSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Name" = _t, Prop1 = _t, Prop2 = _t, Prop3 = _t, Prop4 = _t, Prop5 = _t, #"Desired Property" = _t, Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Value", each try Table.Column(Source,[Desired Property]){List.PositionOf(List.Select(Table.ColumnNames(Source),(x)=>Text.Start(x,4)="Prop"),[Desired Property])} otherwise null)
in
    #"Added Custom"

[marcelmunk]

Thanks for your answer, @Vijay_A_Verma 

The Prop1..PropN are just an example, unfortunatey. They actually have names such as Discipline, Nominal_Diameter, etc. It was just a way to simplify the understanding.

Is it possible to write a formula that would look up all the column names and match for the results?

Thanks again

[Vijay_A_Verma]

In this case, use below formula

= try Table.Column(Source,[Desired Property]){List.PositionOf(Table.ColumnNames(Source),[Desired Property])} otherwise null

[marcelmunk]

@Vijay_A_Verma , I'm getting null for the 3rd row. Should be a 'Z'. Can't figure out why:

[marcelmunk]

Looking closer, the result for first row, 'Smooth' property is on the second row, not first. And the result 'Green' should be for the first row, not second.
Somehow it's messing up rows and colums.

[Vijay_A_Verma]

Then use this

I misunderstood your requirement. Use this

= Table.Column(Source,[Desired Property]){List.PositionOf(Source[Desired Property],[Desired Property])}

[marcelmunk]

Now it looks great on the test query:

But it still givs me an error on the actual data:

The error msg is 'A cyclic reference was found during evaluation'

[Vijay_A_Verma]

Let's try to do this through an Index column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIvSk3NA9LBGYlFBUDaFYiNTYBEBBAHFOUXGAFppVidaCUnIMMppzQVpDg3P78kA8jwBWIzcyARCVVtDFPtDGQEpaYASefSkpLMvHQgywOILSyBRBRUtSlYdSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Name" = _t, Prop1 = _t, Prop2 = _t, Prop3 = _t, Prop4 = _t, Prop5 = _t, #"Desired Property" = _t, Column1 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Value", each Table.Column(#"Added Index",[Desired Property]){[Index]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

[marcelmunk]

Apparentlu it's working. Will test with more data. Thanks a lot.