Forum Discussion

imranamikhan's avatar
imranamikhan
Helper V
3 years ago
Solved

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.

 

 

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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.

  • deevaker's avatar
    deevaker
    Resolver I
    3 years ago

    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
    [email protected]

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