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))
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.

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Then use this

I misunderstood your requirement. Use this

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

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

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

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