cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smpa01 Established Member
Established Member

Custom Lookup

Hello experts,

 

I have a table (raw data) as following

 

IndexCAT1CAT1ValueCAT2CAT2ValueCAT3CAT3Value
1CAT1100CAT2200CAT3300
2CAT1500CAT2100CAT3200
3CAT1200CAT2700CAT3100

 

I need an output with a column which will give me the max of CAT1,CAT2and CAT3 value as following

 

IndexCAT1CAT1ValueCAT2CAT2ValueCAT3CAT3ValueMax_CAT1_CAT2_CAT3
1CAT1100CAT2200CAT3300300
2CAT1500CAT2100CAT3200500
3CAT1200CAT2700CAT3100700

 

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

 

IndexCAT1CAT1ValueCAT2CAT2ValueCAT3CAT3ValueMax_CAT1_CAT2_CAT3Max Based on
1CAT1100CAT2200CAT3300300CAT3
2CAT1500CAT2100CAT3200500CAT1
3CAT1200CAT2700CAT3100700CAT2

 

Desired Final Output

 

Is it possible to achieve in M (not DAX)?

 

Thank you in advance.

 

4 ACCEPTED SOLUTIONS

Accepted Solutions
NickNg278 Senior Member
Senior Member

Re: Custom Lookup

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

Re: Custom Lookup

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 Established Member
Established Member

Re: Custom Lookup

@NickNg278this is awesome mate. Thanks !!!

NickNg278 Senior Member
Senior Member

Re: Custom Lookup

@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]}))}
7 REPLIES 7
NickNg278 Senior Member
Senior Member

Re: Custom Lookup

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

Re: Custom Lookup

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 Established Member
Established Member

Re: Custom Lookup

@NickNg278this is awesome mate. Thanks !!!

smpa01 Established Member
Established Member

Re: Custom Lookup

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

smpa01 Established Member
Established Member

Re: Custom Lookup

@NickNg278I have a follow-up question for you. How can I adapt your solution to the following table

 

 

IndexCAT1CAT1ValueCAT2CAT2ValueCAT3CAT3ValueMax_CAT1_CAT2_CAT3Max Based on
1Reactive100Proactive200Contractual300300Contractual
2Reactive500Proactive100Contractual200500Reactive
3Reactive200Proactive700Contractual100700Proactive

 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"
NickNg278 Senior Member
Senior Member

Re: Custom Lookup

@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 Established Member
Established Member

Re: Custom Lookup

This is great. Thanks @NickNg278