Group By Query

[Apollo71]

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    Service Date       Service Engineer

1234                     1/1/24                 Bill

1234                     1/2/24                 Fred

1234                     1/3/24                 Joe

If I group by serial number and use MAX of service date I get the right grouping but cannot figure out a way to show the engineer name?

Any guidance would be much appreciated

[Ashish_Mathur]

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"

[Anonymous]

Hi @Apollo71 ,

@ryan_mayu Good answer!
And if you must want to use Group by, you can try this way:
First, Duplicate the Table:

In the new table Table(2), use Group by:

Then in the Table(2), click Merge Queries as New:

Configure as shown in the following figure:

Match the Serial number column of the two tables, and have the Max and Service Date columns match.
The output is as below:

Click here to expand the Table column:

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

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

[kpost]

What is your data source?  Is it all coming from a single place or no?

If it is coming from multiple places then you may have to look at the connections between your data tables.

Can you upload a sample .pbix file with some bogus data (if the data is confidential) that simulates the problem? 

It's working fine for me, assuming all of the data is coming from a single document:

https://files.catbox.moe/m03b75.pbix

//Mediocre Power BI Advice,  but it's free//

[Apollo71]

Thanks,

Managed to do what I needed by creating a rank column and grouping by the highest date