Forum Discussion

Harvey85's avatar
Harvey85
Helper I
2 years ago
Solved

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/202...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    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.

_AAndrade's avatar
_AAndrade
Resident Rockstar
2 years ago

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: