cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## 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:

• Buildings have differing year-end dates (for the cost year), e.g., 31-12-2023, 31-03-2024
• I don't always have up to date records to compare. So, for one building it could be 31-12-2023 with 31-12-2022 and for another 31-03-2022 with 31-03-2021.
• There will be differing numbers of records, so I might have two years worth of data for one building and five for another
• Costs are split into separate schedules, so I could have just one schedule for one building and three for another. The schedules have to be kept separate, as an occupier will pay a different percentages for each.

I have included a small simplified table of sample data that shows what I am trying to analyse:

 Building DateEnd ScheduleNo Total Management Security Cleaning Repairs A 31/12/2023 1 700000 70000 280000 210000 140000 A 31/12/2023 2 300000 30000 120000 90000 60000 A 31/12/2023 3 100000 10000 40000 30000 20000 A 31/12/2022 1 620000 62000 248000 186000 124000 A 31/12/2022 2 250000 25000 100000 75000 50000 A 31/12/2022 3 80000 8000 32000 24000 16000 B 31/03/2024 1 500000 50000 200000 150000 100000 B 31/03/2024 2 150000 15000 60000 45000 30000 B 31/03/2023 1 450000 45000 180000 135000 90000 B 31/03/2023 2 125000 12500 50000 37500 25000

I have managed to extract all of the rows with the most recent date using the List.Max function, but I am struggling to extract the second most recent date. I have tried List.MaxN, but as there are multiple rows for some of the buildings, due to the schedules, this doesn't work.

I have been thinking that if I could somehow earmark and exclude the most recent dated records for each building, then I would be left with the next most recent, but I have been able to figure out how to implement this.

Any ideas would be greatly appreciated.

1 ACCEPTED SOLUTION
Super User

see my video

8 REPLIES 8
Super User

Hi @timbo1966 ,

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdDNDcAgCAbQXTiblB9te23XMO6/RoXKDSThgC98CfYODxQQOogPRpY50OwLtWCUwFkfNm4ZifPKPzll65ava/wdM630tnGL59zFrzd+f0ZRrh6Psfvn1Ra7H1/X/vgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Building = _t, DateEnd = _t, ScheduleNo = _t, Total = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"DateEnd", type date}, {"ScheduleNo", Int64.Type}, {"Total", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Building", "ScheduleNo"}, {{"Grouped", each _, type table [Building=nullable text, DateEnd=nullable date, ScheduleNo=nullable number, Total=nullable number]}}),
sorted = Table.Sort([Grouped],{{"DateEnd", Order.Descending}})
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"DateEnd", "Total", "Index"}, {"DateEnd", "Total", "Index"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Index] <> 3)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Grouped"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"DateEnd", type date}, {"Total", type number}})
in
#"Changed Type1"``````

Warning: This can be very slow on a large table. I personally prefer using DAX for this kind of scenario as it is more optimized at scanning a table than PQ.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Regular Visitor

Thank you for taking the time to look at this. I am sure this will work, but the solution from @Ahmedx was easier for me to understand with my relatively basic PowerBI skills.

Super User

see my video

Regular Visitor

That is amazing! Does exactly what I need. Thank you so much for taking the time.

Super User

Hi,

Show the expected result very clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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.

Super User

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]}}),
#"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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

TOP2.pbix

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors