cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smpa01 Senior Member
Senior 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)
Highlighted
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 Senior Member
Senior 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)
Highlighted
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 Senior Member
Senior Member

Re: Custom Lookup

@NickNg278this is awesome mate. Thanks !!!

smpa01 Senior Member
Senior Member

Re: Custom Lookup

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

smpa01 Senior Member
Senior 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 Senior Member
Senior Member

Re: Custom Lookup

This is great. Thanks @NickNg278

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 241 members 2,662 guests
Please welcome our newest community members: