Group by ID, but also need Latest date and last served customer

[Anonymous]

Hi Experts,

Please find the file with attempted PQ steps:    Click for a example file created 

I have ID with salespersons repeated, the count of transactions, Date of service, and last customer served.

Please can I get ideas on how to group by to get the count of transactions in sum ( a small change from example attached, Instead of counting rows, I need the sum of the count? 

2. Along with MAX (Date) which is the latest date of transaction for each user AND 

3. Latest customer served.,

I tried the following:

1. Grop by Name ( I am yet to make changes for SUM of count not count of rows)

2, Then I duplicated the query and I modified the M code with "Date" and list.max([date]) 

3. Then I joined them 

4. I appended the queries, I have all the data but not usable. 😞 

Please can you help ? 

[Anonymous]

@AlexisOlson  Thank you Alexis as always you are the guru. Yes that is what I am looking for, I will test it sir

[AlexisOlson · Accepted solution]

Is this the result you're looking for?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Count", Int64.Type}, {"Last Modified Date", type datetime}, {"Customer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Count", each List.Sum([Count]), type nullable number}, {"All Rows", each Table.Max(_, "Last Modified Date"), type record}}),
    #"Expanded All Rows" = Table.ExpandRecordColumn(#"Grouped Rows", "All Rows", {"Last Modified Date", "Customer"}, {"Last Modified Date", "Customer"})
in
    #"Expanded All Rows"