Find a column name

[AndreasFrank]

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	3	3	Option 3
d	34	12	7	34	Option 1

For each lineitem I select a value from one of the 3 available options. So for lineitem "a" I selected value 11 which belongs to Option 2.

Now I'd like to add a column "From Option" that shows the name of the column from which the value was selected. Is there any formular that can search through the columns of a row to find a value and then return the name of the respective column?

Thank you!
Andreas

[Vera_33 · Accepted solution]

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]

Hi Vera!

It works, but I have no clue how 🙂

Thank you for the quick support!!

Best regards!
Andreas

[Vera_33]

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