Forum Discussion

Apollo71's avatar
Apollo71
New Member
2 years ago
Solved

Group By Query

Hi,   I am trying to create a Power BI form with the following data, but group by serial number and showing the latest service date and corresponding name of the engineer.   Serial number    Serv...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    here is a workaround for you

    try to create a rank column

    = Table.AddColumn(#"Changed Type","Rank",each Table.RowCount(Table.Distinct(Table.SelectRows(#"Changed Type",(x)=>x[SD]>[SD] and x[SN]=[SN]),"SD"))+1)

     

    then filter rank =1

     

     

    at last you can delete rank column

    pls see the attachment below

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial number", Int64.Type}, {"Service Date", type date}, {"Service Engineer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Serial number"}, {{"Count", each Table.Max(_,"Service Date")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Service Date", "Service Engineer"}, {"Service Date", "Service Engineer"})
in
    #"Expanded Count"