Max value from a column in Power Query Editor for each value in another column

[NanardPet]

Hi there,

I would like to select all the rows of a table for which, for each different record, the fiscal year corresponds to the last year of this record. Here is the expected result in Excel:

Record	FiscalYear	MaxOfFiscalYear	Select
GE	2016	2021	FAUX
GE	2017	2021	FAUX
GE	2018	2021	FAUX
GE	2019	2021	FAUX
GE	2020	2021	FAUX
GE	2021	2021	VRAI
ADE	2013	2020	FAUX
ADE	2014	2020	FAUX
ADE	2015	2020	FAUX
ADE	2016	2020	FAUX
ADE	2017	2020	FAUX
ADE	2018	2020	FAUX
ADE	2019	2020	FAUX
ADE	2020	2020	VRAI

Many thanks in advance for a solution

Bernard

[m_alireza · Accepted solution]

Hi @NanardPet 

You can achieve this through the group by function in Power Query. Copy and paste this in your advanced editor and amend as needed: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcndV0lEyMjA0U4rVQfDMUXgWKDxLZJ6RAQrPEMxzdIEqNUblmqByTVG5Zqhcc1SuBSrXEoULckUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Record = _t, FiscalYear = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record", type text}, {"FiscalYear", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Record"}, {{"MaxOfFiscalYear", each List.Max([FiscalYear]), type nullable number}, {"AllRows", each _, type table [Record=nullable text, FiscalYear=nullable number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"FiscalYear"}, {"FiscalYear"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded AllRows", "Select", each if [FiscalYear] = [MaxOfFiscalYear] then true else false)
in
    #"Added Conditional Column"

Sample output: 

[NanardPet · Accepted solution]

Thanks a lot m_alireza : it works fine!

[m_alireza · Accepted solution]

Hi @NanardPet 

You can achieve this through the group by function in Power Query. Copy and paste this in your advanced editor and amend as needed: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcndV0lEyMjA0U4rVQfDMUXgWKDxLZJ6RAQrPEMxzdIEqNUblmqByTVG5Zqhcc1SuBSrXEoULckUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Record = _t, FiscalYear = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record", type text}, {"FiscalYear", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Record"}, {{"MaxOfFiscalYear", each List.Max([FiscalYear]), type nullable number}, {"AllRows", each _, type table [Record=nullable text, FiscalYear=nullable number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"FiscalYear"}, {"FiscalYear"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded AllRows", "Select", each if [FiscalYear] = [MaxOfFiscalYear] then true else false)
in
    #"Added Conditional Column"

Sample output: