Forum Discussion
Luke_Lee
Mar 25, 2021Copper 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 ...
- Mar 25, 2021
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"
SergeiBaklan
Mar 25, 2021MVP
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"
- SODERKOct 04, 2023Copper ContributorThank 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- SergeiBaklanOct 08, 2023MVP
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.
- peiyezhuOct 04, 2023Bronze ContributorIf you upload an Excel workbook with dummy data and expected result,it is easier to settle specific problem.