Forum Discussion

marcelmunk's avatar
marcelmunk
Frequent Visitor
4 years ago
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 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

 

Need Help
  • NickOP's avatar
    NickOP
    4 years ago

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

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

11 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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's avatar
      marcelmunk
      Frequent Visitor
      4 years ago

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        In this case, use below formula

        = try Table.Column(Source,[Desired Property]){List.PositionOf(Table.ColumnNames(Source),[Desired Property])} otherwise null
  • NickOP's avatar
    NickOP
    Frequent Visitor
    4 years ago

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

    = Table.AddColumn(Source, "Value", each Record.FieldOrDefault(_,[Desired Property], null))
  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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"