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

[Luke_Lee]

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

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

[#2]

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

[#3 · Accepted solution]

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

[#4]

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.

[#5]

Thank you dear Sergei your code is very useful.
I would like to get a column value from another table with condition to check two columns values. Assume. Table1 has MaxDate, NameID fields and
Table2 has NMaxDate, NameID, DepID fields.  In this case, Table2's NMaxDate has more dates in the table.
I need  DepID field into Table1 as new custom column from Table2, condition is two columns equals: Table1[MaxDate] = Table2[NMaxDate] and Table1[NameID] = Table2[NameID]. 

please help me how to get it with MQuery. 
Tank you 

Very much 

[#6]

If you upload an Excel workbook with dummy data and expected result,it is easier to settle specific problem.

[#7]

@SODERK 

For such sample

if you mean Power Query you may query Table1 and Table2, merge them as new query on NameID and dates; load result into the grid.

Alternatively you may add to Table1 department column which is calculated as

=XLOOKUP([@NameID]&[@MaxDate], Table2[NameID]&Table2[NMaxDate], Table2[DeptID], "not defined" )

Please check attached file.