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 curre...
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.

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/