Forum Discussion

Harvey85's avatar
Harvey85
Icon for Helper I rankHelper 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.

Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
2 years ago

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"