[Power Query] How to find Max Value with condition in another column

Hi, If any one know how to find max value with a conditional selection in another column? (Power Query only) Example like below, try to find "Column 1 = B" + maximum value in column 2 ...

excel

SergeiBaklan

Mar 25, 2021

Luke_Lee

In addition to Subodh_Tiwari_sktneer solution for column maximum here are couple of filtered maximums

for external filter

let
    filter = "A",
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {{"Column1", type text}, {"Column2", Int64.Type}}
    ),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "maxExt",
        each
            [
                max=Table.Group(
                    Source, {"Column1"},
                    {{"MaxFiltered", each List.Max([Column2])}}
                ){[Column1=filter]}[MaxFiltered]
            ][max]
    )
in
    #"Added Custom"

max per group

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {{"Column1", type text}, {"Column2", Int64.Type}}
    ),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "maxInt",
        each
            [
                filter = [Column1],
                max=Table.Group(
                    Source, {"Column1"},
                    {{"MaxFiltered", each List.Max([Column2])}}
                ){[Column1=filter]}[MaxFiltered]
            ][max]
    )
in
    #"Added Custom"

PQ_ColumnMax.xlsx24 KB

Subodh_Tiwari_sktneer

Silver Contributor

Mar 25, 2021

Luke_Lee

Assuming the name of the source data table is Table1, insert a blank query with the following code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max(#"Changed Type"[Column2]))
in
    #"Added Custom"

Refer to the attached for more details.

PQ_ColumnMax.xlsx17 KB