Forum Discussion
Solved
Custom Lookup
Hello experts, I have a table (raw data) as following Index CAT1 CAT1Value CAT2 CAT2Value CAT3 CAT3Value 1 CAT1 100 CAT2 200 CAT3 300 2 CAT1 500 CAT2 100 CAT3 200 ...
smpa01,
You can create the custom column with this formula below"CAT" & Text.From(List.PositionOf({[CAT1Value], [CAT2Value], [CAT3Value]}, List.Max({[CAT1Value],[CAT2Value],[CAT3Value]})) + 1)Hi smpa01
Try this:
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"CAT1", type text}, {"CAT1Value", Int64.Type}, {"CAT2", type text}, {"CAT2Value", Int64.Type}, {"CAT3", type text}, {"CAT3Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Max_CAT1_CAT2_CAT3", each List.Max({[CAT1Value],[CAT2Value],[CAT3Value]})), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [CAT1Value] = [Max_CAT1_CAT2_CAT3] then "CAT1" else if [CAT2Value] = [Max_CAT1_CAT2_CAT3] then "CAT2" else if [CAT3Value] = [Max_CAT1_CAT2_CAT3] then "CAT3" else null)
in #"Added Custom1"Anonymousthis is awesome mate. Thanks !!!
Actually this is a better solution for the custom column to adapt to your table
{[CAT1], [CAT2], [CAT3]}{List.PositionOf({[CAT1Value], [CAT2Value], [CAT3Value]}, List.Max({[CAT1Value],[CAT2Value],[CAT3Value]}))}
AnonymousI have a follow-up question for you. How can I adapt your solution to the following table
| Index | CAT1 | CAT1Value | CAT2 | CAT2Value | CAT3 | CAT3Value | Max_CAT1_CAT2_CAT3 | Max Based on |
| 1 | Reactive | 100 | Proactive | 200 | Contractual | 300 | 300 | Contractual |
| 2 | Reactive | 500 | Proactive | 100 | Contractual | 200 | 500 | Reactive |
| 3 | Reactive | 200 | Proactive | 700 | Contractual | 100 | 700 | Proactive |
With the table above AlB's solution can still be applied as below
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"CAT1", type text}, {"CAT1Value", Int64.Type}, {"CAT2", type text}, {"CAT2Value", Int64.Type}, {"CAT3", type text}, {"CAT3Value", Int64.Type}, {"Max_CAT1_CAT2_CAT3", Int64.Type}, {"Max Based on", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Max Based on"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom.1", each if [CAT1Value]= List.Max({[CAT1Value],[CAT2Value],[CAT3Value]}) then [CAT1] else if [CAT2Value]=List.Max({[CAT1Value],[CAT2Value],[CAT3Value]}) then [CAT2] else if [CAT3Value]=List.Max({[CAT1Value],[CAT2Value],[CAT3Value]}) then [CAT3] else null)
in
#"Added Custom2"