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
Ashish_Mathur
Super User
Super UserHi,
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.
- Ashish_Mathur
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.
