Forum Discussion
Bring Value for Max Date by Key in Power Query
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!!!
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.
5 Replies
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: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"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
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.
