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
Hi,
Show the expected result very clearly.
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.
Hi,
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.
