Forum Discussion

Luke_Lee's avatar
Luke_Lee
Copper Contributor
Mar 25, 2021
Solved

[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

 

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

 

excel
  • SergeiBaklan's avatar
    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"

6 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    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"
    • SODERK's avatar
      SODERK
      Copper Contributor
      Oct 04, 2023
      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 

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Oct 08, 2023

        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.

  • Subodh_Tiwari_sktneer's avatar
    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.

     

     

    • Luke_Lee's avatar
      Luke_Lee
      Copper Contributor
      Mar 26, 2021

      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.

       

Resources