SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2234694%22%20slang%3D%22en-US%22%3E%5BPower%20Query%5D%20How%20to%20find%20Max%20Value%20with%20condition%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2234694%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20any%20one%20know%20how%20to%20find%20max%20value%20with%20a%20conditional%20selection%20in%20another%20column%3F%20(Power%20Query%20only)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20like%20below%2C%20try%20to%20find%20%22Column%201%20%3D%20B%22%20%2B%20maximum%20value%20in%20column%202%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Luke_Lee_0-1616664618936.png%22%20style%3D%22width%3A%20299px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266941i26E1D262D24D2859%2Fimage-dimensions%2F299x206%3Fv%3Dv2%22%20width%3D%22299%22%20height%3D%22206%22%20role%3D%22button%22%20title%3D%22Luke_Lee_0-1616664618936.png%22%20alt%3D%22Luke_Lee_0-1616664618936.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExpect%20result%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Luke_Lee_1-1616664852425.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266942i368B264E8497E311%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Luke_Lee_1-1616664852425.png%22%20alt%3D%22Luke_Lee_1-1616664852425.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20group%20data%20might%20be%20a%20solution%20but%20I%20wish%20to%20set%20M%20language%20in%201%20step%20only%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2234694%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2234795%22%20slang%3D%22en-US%22%3ERe%3A%20%5BPower%20Query%5D%20How%20to%20find%20Max%20Value%20with%20condition%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2234795%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F992520%22%20target%3D%22_blank%22%3E%40Luke_Lee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20the%20name%20of%20the%20source%20data%20table%20is%20Table1%2C%20insert%20a%20blank%20query%20with%20the%20following%20code.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(Source%2C%7B%7B%22Column1%22%2C%20type%20text%7D%2C%20%7B%22Column2%22%2C%20Int64.Type%7D%7D)%2C%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%23%22Changed%20Type%22%2C%20%22Custom%22%2C%20each%20List.Max(%23%22Changed%20Type%22%5BColumn2%5D))%0Ain%0A%20%20%20%20%23%22Added%20Custom%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERefer%20to%20the%20attached%20for%20more%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.