Forum Discussion

AndreasFrank's avatar
AndreasFrank
New Member
4 years ago
Solved

Find a column name

Dear PowerBI Community!   I have a table like this:   Lineitem Option 1 Option 2 Option 3 Selected Value From Option a 14 11 9 11 Option 2 b 3 14 7 7 Option 3 c 7 56 ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi AndreasFrank 

     

    Not sure if I made it too complicated

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0ARGGQMISwojViVZKAjKNYZLmYAwSTobyTM2g8sZg4RQQC2yMEVQBkBcbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lineitem = _t, #"Option 1" = _t, #"Option 2" = _t, #"Option 3" = _t, #"Selected Value" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "OptionFrom",  (x)=>
[a= Table.SelectRows(Source,each [Lineitem]=x[Lineitem]),
colName = List.Select( Table.ColumnNames(a), each Text.Contains(_,"Option")),
b=Table.Transpose( Table.DemoteHeaders( Table.SelectColumns(a, colName))),
c=Table.SelectRows(b,each [Column2]=x[Selected Value])[Column1]{0}?][c])
in
    #"Added Custom"
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi AndreasFrank 

 

Not sure if I made it too complicated

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0ARGGQMISwojViVZKAjKNYZLmYAwSTobyTM2g8sZg4RQQC2yMEVQBkBcbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lineitem = _t, #"Option 1" = _t, #"Option 2" = _t, #"Option 3" = _t, #"Selected Value" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "OptionFrom",  (x)=>
[a= Table.SelectRows(Source,each [Lineitem]=x[Lineitem]),
colName = List.Select( Table.ColumnNames(a), each Text.Contains(_,"Option")),
b=Table.Transpose( Table.DemoteHeaders( Table.SelectColumns(a, colName))),
c=Table.SelectRows(b,each [Column2]=x[Selected Value])[Column1]{0}?][c])
in
    #"Added Custom"
AndreasFrank's avatar
AndreasFrank
New Member
4 years ago

Hi Vera!

 

It works, but I have no clue how 🙂

 

Thank you for the quick support!!

 

Best regards!
Andreas

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi AndreasFrank 

     

    Here is another way, use a custom function called fnSwitch

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0ARGGQMISwojViVZKAjKNYZLmYAwSTobyTM2g8sZg4RQQC2yMEVQBkBcbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lineitem = _t, #"Option 1" = _t, #"Option 2" = _t, #"Option 3" = _t, #"Selected Value" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "OptionFrom",  (x)=>
[a= Table.SelectRows(Source,each [Lineitem]=x[Lineitem]),
b=List.Transform( Table.ToRecords(a),Record.ToTable){0},
fnSwitch = (input as text) => [
        values =List.Zip ({
        b[Name],b[Value]
        }),
        Result = List.First(List.Select(values, each _{1}=input)){0}][Result],
c= fnSwitch(x[Selected Value])][c])
in
    #"Added Custom"

     

    Read his blog to understand M better

    Power Query M Primer (part 1): Introduction, Simple Expressions & let | Ben Gribaudo