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

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

       

      • marcelmunk's avatar
        marcelmunk
        Frequent Visitor
        4 years ago

        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.