Forum Discussion
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 |
| 3 | CAT1 | 200 | CAT2 | 700 | CAT3 | 100 |
I need an output with a column which will give me the max of CAT1,CAT2and CAT3 value as following
| Index | CAT1 | CAT1Value | CAT2 | CAT2Value | CAT3 | CAT3Value | Max_CAT1_CAT2_CAT3 |
| 1 | CAT1 | 100 | CAT2 | 200 | CAT3 | 300 | 300 |
| 2 | CAT1 | 500 | CAT2 | 100 | CAT3 | 200 | 500 |
| 3 | CAT1 | 200 | CAT2 | 700 | CAT3 | 100 | 700 |
The M is as following for the output above
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]}))
in
#"Added Custom"However, I want to add another column which will tell the readers on which column the Max Value is based on
| Index | CAT1 | CAT1Value | CAT2 | CAT2Value | CAT3 | CAT3Value | Max_CAT1_CAT2_CAT3 | Max Based on |
| 1 | CAT1 | 100 | CAT2 | 200 | CAT3 | 300 | 300 | CAT3 |
| 2 | CAT1 | 500 | CAT2 | 100 | CAT3 | 200 | 500 | CAT1 |
| 3 | CAT1 | 200 | CAT2 | 700 | CAT3 | 100 | 700 | CAT2 |
Desired Final Output
Is it possible to achieve in M (not DAX)?
Thank you in advance.
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]}))}
7 Replies
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 !!!
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"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]}))}