2 last records in power query

[pakovic]

Good morning, Thank you in advance for reading my case. I have a list of names and what these people have spent in several years.

is it possible to transform a table in powerquery to display the last 2 records for each name?

Logically, people who only have 1 record, show that record. Attached pbix example

attached pbix

https://www.dropbox.com/s/l8vhureetptsn0g/test%20%282%29.pbix?dl=0

Name    Mount   Year

andres	1250	2020
andres	2120	2021
andres	4584	2022
andres	5466	2023
felipe	1365	2020
felipe	1211	2021
jesus	1520	2020
juan	2000	2020
juan	452	2021
pepe	1000	2019
pepe	1500	2021
pepe	6554	2022

transform to:

Name    Mount   Year

andres	4584	2022
andres	5466	2023
felipe	1365	2020
felipe	1211	2021
jesus	1520	2020
juan	2000	2020
juan	452	2021
pepe	1500	2021
pepe	6554	2022

[pakovic]

¡wow! Thankyou very much... GSun!!  Your are incredible.

[Anonymous]

@HotChilli 's solution is better than mine

[Anonymous · Accepted solution]

let
    Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7RCoMwDEX/Jc8+JLG3zG8pfRCWwWSIKP6/rq2uhT2VnpN7kxBoscWoI2Hm81GWgWIXaNrHOf0LVk74msaFJeFxfq62fYWimX/Z550TvUctJtv2FIA2gbtJRZsV5SAHremvX0VqUQ71gMtY23qHx38B530WPcV4AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Mount = _t, Year = _t]),
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Mount", Int64.Type}}),
    Personalizado1 = Table.Buffer(Table.Sort(#"Tipo cambiado",{{"Name", Order.Descending}})),
    #"Grouped Rows" = Table.Group(Personalizado1, {"Name"}, {{"Record", each _, type table [Name=nullable text, Mount=nullable number, Year=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Last2", each Table.LastN([Record], 2)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Last2"}),
    #"Expanded Last2" = Table.ExpandTableColumn(#"Removed Other Columns", "Last2", {"Name", "Mount", "Year"}, {"Name", "Mount", "Year"})
in
    #"Expanded Last2"

[HotChilli]

If you sort by Name and then Year, you can add an index column for each Name group. Try and find this out yourself (on this forum or there are plenty of examples out there on the web.  I will help if you get stuck).

Then filter the new Index column to keep only Index = 1 or 2