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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
imranamikhan
Helper V
Helper V

Get second highest value per group in Power Query

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.

 

imranamikhan_0-1665886249446.png

 

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

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png


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

View solution in original post

4 REPLIES 4
imranamikhan
Helper V
Helper V

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.

You are welcome.


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

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])))
 
deevaker_0-1665902800689.png

 

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]))
deevaker_1-1665902940408.png

 

Thanks,

Deevaker Goel
+919711975011
Deevaker@hotmail.com

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

Ashish_Mathur
Super User
Super User

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.

Untitled.png


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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