Custom Lookup

[smpa01]

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.

[Anonymous · Accepted solution]

@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)

[AlB · Accepted solution]

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"

[smpa01 · Accepted solution]

@Anonymousthis is awesome mate. Thanks !!!

[Anonymous · Accepted solution]

@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)

[smpa01]

@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"

[Anonymous · Accepted solution]

@smpa01,

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]}))}

[smpa01]

This is great. Thanks @Anonymous

[smpa01 · Accepted solution]

@Anonymousthis is awesome mate. Thanks !!!

[AlB · Accepted solution]

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"

[smpa01]

@AlBthis is simple. Too bad did not strike me before. Thanks anyway mate.