SOLVED

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

Occasional Contributor

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

Luke_Lee_0-1616664618936.png

 

Expect result: (please note I want it return to all fields)

Luke_Lee_1-1616664852425.png

 

3 Replies

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

 

 

best response confirmed by Luke_Lee (Occasional Contributor)
Solution

@Luke_Lee 

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

image.png

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"

Hi, @Subodh_Tiwari_sktneer 

 

Your solution is more on max value in total column 2, actually I want filter column 1 first and then check max value.