Forum Discussion
Solved
Retrieve value from same row based on column name in column
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 ...
Here's an alternative solution that reads the record itself:
= Table.AddColumn(Source, "Value", each Record.FieldOrDefault(_,[Desired Property], null))
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 nullSee 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"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
In this case, use below formula
= try Table.Column(Source,[Desired Property]){List.PositionOf(Table.ColumnNames(Source),[Desired Property])} otherwise null
