Forum Discussion
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 | 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
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"
3 Replies
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"Hi Vera!
It works, but I have no clue how 🙂
Thank you for the quick support!!
Best regards!
AndreasHi 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
