Forum Discussion
Solved
Most recent two records with multiple rows
Hi, I am struggling with a problem regarding the analysis of property cost data. I want to be able to compare the most recent two budgets that I have for a number of buildings. The key variables are...
see my video
https://1drv.ms/v/s!AiUZ0Ws7G26Rhz_GpGudZ9ZPoqtg?e=dsWbNL
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rhz6qlA7kcAK3woHZ?e=XGQ2cR
I'll simplify the table, but I probably need a few more rows to show what I mean:
| Building | DateEnd | ScheduleNo | Total |
| A | 31/12/2023 | 1 | 700000 |
| A | 31/12/2023 | 2 | 300000 |
| A | 31/12/2023 | 3 | 100000 |
| A | 31/12/2022 | 1 | 62000 |
| A | 31/12/2022 | 2 | 250000 |
| A | 31/12/2022 | 3 | 80000 |
| A | 31/12/2021 | 1 | 580000 |
| A | 31/12/2021 | 2 | 220000 |
| A | 31/12/2021 | 3 | 70000 |
| B | 31/03/2024 | 1 | 500000 |
| B | 31/03/2023 | 1 | 450000 |
| B | 31/03/2022 | 1 | 400000 |
Afterward I would want to end up with the following:
| Building | DateEnd | ScheduleNo | Total |
| A | 31/12/2023 | 1 | 700000 |
| A | 31/12/2023 | 2 | 300000 |
| A | 31/12/2023 | 3 | 100000 |
| A | 31/12/2022 | 1 | 62000 |
| A | 31/12/2022 | 2 | 250000 |
| A | 31/12/2022 | 3 | 80000 |
| B | 31/03/2024 | 1 | 500000 |
| B | 31/03/2023 | 1 | 450000 |
So, the 2021 dated rows for Building A and the 2022 dated row for Building B are excluded.
Ashish_Mathur
Super User
Super UserHi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"DateEnd", type date}, {"ScheduleNo", Int64.Type}, {"Total", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Building"}, {{"Grouped", each _, type table [Building=nullable text, DateEnd=nullable date, ScheduleNo=nullable number, Total=nullable number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddRankColumn([Grouped],"Rank",{"DateEnd",Order.Descending},[RankKind = RankKind.Dense])),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"DateEnd", "ScheduleNo", "Total", "Rank"}, {"DateEnd", "ScheduleNo", "Total", "Rank"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom.1", each [Rank] <= 2),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Grouped", "Rank"})
in
#"Removed Columns"
Hope this helps.