I have a column of data in Excel/Power Bi. It has 2 maximum values. I wish to create a second column with the same values as the first column, except add 1 to only one of the maximum values, using power query (M Code). Acknowledge if I only had one maximum value, it would be a relatively simple List.Max/Table.AddColumn exercise. But in this case, I have 2 maximum values. How do I distinguish between the 2 maximum values. See link below for sample data.

I have a column of data in Excel/Power Bi. It has 2 maximum values. I wish to create a second column with the same values as the first column, except add 1 to only one of the maximum values, using power query (M Code). Acknowledge if I only had one maximum value, it would be a relatively simple List.Max/Table.AddColumn exercise. But in this case, I have 2 maximum values. How do I distinguish between the 2 maximum values. See link below for sample data.

• powerbi
• powerquery
• powerbi-desktop
• m

2 Answers 2

You can do all this from the UI

• Add an Index Column

• Add a Custom column with the formula:

  if [Numbers] = List.Max(#"Added Index"[Numbers]) then [Index] else null
  

• Add a second Custom column:

  if [Custom] = List.Min(#"Added Custom"[Custom]) then [Numbers] + 1 else [Numbers]
  

• Remove the excess columns

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", 
        each 
            if [Numbers] = List.Max(#"Added Index"[Numbers]) 
                then [Index] else null),
    
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Add 1", 
        each if [Custom] = List.Min(#"Added Custom"[Custom]) 
            then [Numbers] + 1 else [Numbers]),
    
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"})
in
    #"Removed Columns"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}, {"Add 1 to only one of the max values", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [
        a = List.Max(#"Changed Type"[Numbers]),
        b = List.PositionOf(#"Changed Type"[Numbers],a),
        c = if #"Changed Type"{b} = _ then [Numbers]+1 else [Numbers]
    ][c])
in
    #"Added Custom"