Get second highest value per group in Power Query

[imranamikhan]

Hi everyone,

I have a table with a list of projects. Each project is duplicated per the number of reporting periods. The reporting period is identified by a number column.

I am currently using Power Query to identify the latest record based on this number column. I am using the Group By function with List Max to do this. Is there a way to modify this to identify the 2nd largest? If not, is there an alternate method anyone could suggest?

let

Source = CommonDataService.Database(varDataverse),
    
dbo_cr37a_dataverse_confidence_app_project_entry = Source{[Schema="dbo",Item="cr37a_dataverse_confidence_app_project_entry"]}[Data],

#"Reordered Columns" = Table.ReorderColumns(dbo_cr37a_dataverse_confidence_app_project_entry,{"cr37a_dataverse_confidence_app_project_entryid","createdon", "modifiedon", "createdbyname", "modifiedbyname", "cr37a_projectname", "cr37a_project_code", "cr37a_programme_code", "cr37a_programme_name", "cr37a_responsible_owner_name", "cr37a_responsible_owner_email", "cr37a_project_health_score_previous", "cr37a_project_health_score", "cr37a_project_health_score_forecast", "cr37a_project_health_previous_forecast", "cr37a_plan_score", "cr37a_finance_score", "cr37a_risk_score", "cr37a_resource_score", "cr37a_team_wellbeing_score", "cr37a_overall_score", "cr37a_activities_this_period", "cr37a_activities_next_period", "cr37a_is_approved_by_board", "cr37a_sort_index", "cr37a_reporting_period", "cr37a_reporting_sub_period", "cr37a_plan_score_previous", "cr37a_finance_score_previous", "cr37a_risk_score_previous", "cr37a_resource_score_previous", "cr37a_team_wellbeing_score_previous", "cr37a_activities_previous_period", "cr37a_overall_score_previous"}),

#"Grouped Rows" = Table.Group(#"Reordered Columns", {"cr37a_project_code"}, {{"LatestRecord", each List.Max([cr37a_sort_index]), Int64.Type}, {"All", each _, type table [cr37a_dataverse_confidence_app_project_entryid=text, createdon=nullable datetime, modifiedon=nullable datetime, createdbyname=nullable text, modifiedbyname=nullable text, cr37a_projectname=nullable text, cr37a_project_code=nullable text, cr37a_programme_code=nullable text, cr37a_programme_name=nullable text, cr37a_responsible_owner_name=nullable text, cr37a_responsible_owner_email=nullable text, cr37a_project_health_score_previous=nullable number]}}),

#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"cr37a_projectname", "cr37a_programme_code", "cr37a_programme_name"}, {"cr37a_projectname", "cr37a_programme_code", "cr37a_programme_name"})

in
    #"Expanded All"

Could anyone point me into the right direction?

Best regards,

AmiK

[imranamikhan]

As always thanks for the support @Ashish_Mathur.

@deevaker thanks for the suggestion but there is a specific reason I need an M-Query solution.

[Ashish_Mathur]

You are welcome.

[deevaker]

You can use it in DAX, it depends if you would like to create a separate table out of it or just need a measure.
For a separate summary table you can use below DAX :

SummaryTable =

SUMMARIZE('Table','Table'[Project Name],"Second Largest",if(COUNT('Table'[Reporting Period])>1, Calculate(max('Table'[Reporting Period]),'Table'[Reporting Period]<MAX('Table'[Reporting Period])),MAX('Table'[Reporting Period])))

And if you only need a measure, than write this while creating measure:

Measure = if(COUNT('Table'[Reporting Period])>1, Calculate(max('Table'[Reporting Period]),'Table'[Reporting Period]<MAX('Table'[Reporting Period])),MAX('Table'[Reporting Period]))

Thanks,

Deevaker Goel
+919711975011
Deevaker@hotmail.com

https://www.linkedin.com/in/deevakerg/

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Reporting Period", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project Name"}, {{"2nd latest reporting period", each try List.Sort([Reporting Period],Order.Descending){1} otherwise 1, type nullable number}})
in
    #"Grouped Rows"

Hope this helps.