SOLVED

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

Copper Contributor

# [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

6 Replies

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

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}}),
in

Refer to the attached for more details.

best response confirmed by Luke_Lee (Copper Contributor)
Solution

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

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}}
),
#"Changed Type",
"maxExt",
each
[
max=Table.Group(
Source, {"Column1"},
{{"MaxFiltered", each List.Max([Column2])}}
){[Column1=filter]}[MaxFiltered]
][max]
)
in

max per group

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Column1", type text}, {"Column2", Int64.Type}}
),
#"Changed Type",
"maxInt",
each
[
filter = [Column1],
max=Table.Group(
Source, {"Column1"},
{{"MaxFiltered", each List.Max([Column2])}}
){[Column1=filter]}[MaxFiltered]
][max]
)
in

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

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

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

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

Tank you

Very much

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

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

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

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

1 best response

Accepted Solutions
best response confirmed by Luke_Lee (Copper Contributor)
Solution

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

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}}
),
#"Changed Type",
"maxExt",
each
[
max=Table.Group(
Source, {"Column1"},
{{"MaxFiltered", each List.Max([Column2])}}
){[Column1=filter]}[MaxFiltered]
][max]
)
in

max per group

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Column1", type text}, {"Column2", Int64.Type}}
),
#"Changed Type",
"maxInt",
each
[
filter = [Column1],
max=Table.Group(
Source, {"Column1"},
{{"MaxFiltered", each List.Max([Column2])}}
){[Column1=filter]}[MaxFiltered]
][max]
)
in