Bring Value for Max Date by Key in Power Query

[Harvey85]

Hello,

I have the following table in Power Query:

Inspections:

Key		Date		Value
AA		03/20/2024		100
AA		02/15/2024		80
AA		02/27/2024		50
BB		02/27/2024		50
BB		02/15/2024		80
CC		03/20/2024		35

What I need is to get in Power Query, for each Key, the Value for the max Date:

Expected Output:

Key		Date		Value
AA		03/20/2024		100
BB		02/27/2024		50
CC		03/20/2024		35

Is there any simple way to do that using Power Query, preferibly by Drag & Drop or any button in Power Query?

I've tried the Group by button but I think it doesn't work in this case:

Or maybe just group by Key and Max(Date)... and then Join the original table by Key+Date and add the field Value? Or is there any simplier way to do that?

Thank you!!!

[Harvey85]

Hi @_AAndrade do you have the PBIX or screen capture from steps  "GroupByMaxDate" and "Merge"? Because I have a spanish installation of Power BI and I'm struggling finding the same buttons you used to build the solution. Thank you!!!

[_AAndrade · Accepted solution]

Take a look at this gif:

The most complicated part is the merge stage. You have to merge with the table itself, but in the ChangeType step. To choose more than one column to link the two tables, you have to use ctrl + left click in the same order in both tables, as I did in the gif.

[Tom_Y]

How about something low code?

Click and sort "Value", decending, and then sort "Key", you can see the small numbers, see that line of code, even it's by clicking.

and then click key, and remove duplicate

[Ahmedx]

pls try this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEyMNY3MgAiIxMgx9DAQClWByZjpG9oCpOxQJUwModJmEIknJwISGAY5eyMbruxqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Date = _t, Value = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Date", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(ChangeType, {"Key"}, {{"Count", (x)=> Table.FromRecords({ Table.Max(x,"Date")})}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Key", "Date", "Value"}, {"Key", "Date", "Value"})
in
    #"Expanded Count"

[_AAndrade]

Hi,

Please try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEyMNY3MgAiIxMgx9DAQClWByZjpG9oCpOxQJUwModJmEIknJwISGAY5eyMbruxqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Date = _t, Value = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Date", type text}, {"Value", Int64.Type}}),
    GroupByMaxDate = Table.Group(ChangeType, {"Key"}, {{"MaxDate", each List.Max([Date]), type nullable text}}),
    Merge = Table.NestedJoin(GroupByMaxDate, {"Key", "MaxDate"}, ChangeType, {"Key", "Date"}, "Merged", JoinKind.Inner),
    ExpandValue = Table.ExpandTableColumn(Merge, "Merged", {"Value"}, {"Value"})
in
    ExpandValue

Change the Source to your own data and the final output should be this: