 SOLVED

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

Occasional 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  3 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 (Occasional 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