Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Copper 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

 

6 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 (Copper 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.

 

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 

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

@SODERK 

For such sample

image.png

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.

1 best response

Accepted Solutions
best response confirmed by Luke_Lee (Copper 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"

View solution in original post