Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
timbo1966
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:

 

BuildingDateEndScheduleNoTotalManagementSecurityCleaningRepairs
A31/12/2023170000070000280000210000140000
A31/12/20232300000300001200009000060000
A31/12/2023310000010000400003000020000
A31/12/2022162000062000248000186000124000
A31/12/20222250000250001000007500050000
A31/12/20223800008000320002400016000
B31/03/2024150000050000200000150000100000
B31/03/2024215000015000600004500030000
B31/03/202314500004500018000013500090000
B31/03/2023212500012500500003750025000

 

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
8 REPLIES 8
danextian
Super User
Super User

Hi @timbo1966 ,

Please try the code below.

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]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let 
sorted = Table.Sort([Grouped],{{"DateEnd", Order.Descending}})
in Table.AddIndexColumn(sorted,"Index",1,1)),
    #"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.
 










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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.

Ahmedx
Super User
Super User

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

Ashish_Mathur
Super User
Super User

Hi,

Show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I'll simplify the table, but I probably need a few more rows to show what I mean:

BuildingDateEndScheduleNoTotal
A31/12/20231700000
A31/12/20232300000
A31/12/20233100000
A31/12/2022162000
A31/12/20222250000
A31/12/2022380000
A31/12/20211580000
A31/12/20212220000
A31/12/2021370000
B31/03/20241500000
B31/03/20231450000
B31/03/20221400000

 

Afterward I would want to end up with the following:

BuildingDateEndScheduleNoTotal
A31/12/20231700000
A31/12/20232300000
A31/12/20233100000
A31/12/2022162000
A31/12/20222250000
A31/12/2022380000
B31/03/20241500000
B31/03/20231450000

 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

TOP2.pbix

 

ThxAlot_0-1682199473831.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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