Only group by max of column in query editor

[Anonymous]

HI all

I am trying to get the data in Power query to which adres of a customer we have done the most deliveries. 

My dat a look as follows

Name	Adres	class	number of delvieries
Jane	abcroad	House	43
Jane	DBCRoad	appartment	1
Joe	BVSavenueu	House	2
Joe	BFQstreet	Getto	50

I would like the output tabel in the query editor  to be like this:

Name	adress	clas	Number of delveriies
Jane	abcroad	House	43
Joe	BFQstreet	Getto	50

Could you help me

[v-zhenbw-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution.

If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks a lot.

Best regards,

Community Support Team _ zhenbw

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

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the Group function and Table.Max() function to meet your requirement.

1. Group the name column, and group all rows.

2. Then add a custom column and expand it.

3. At last, delete the Details column.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Anonymous]

@Anonymous 

Based on your example I added the following steps to see the two customers with the most deliveries: 

#"Sorted Rows" = Table.Sort(#"Changed Type",{{"number of delvieries", Order.Descending}}), // Changed Type was last step in PQ 
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",2)
in
#"Kept First Rows"

Hope this helps. 

[amitchandak]

@Anonymous , In Dax you can have two new measures for last twp columns. Other two you can dag as is

new class =
VAR __id = MAX ( 'Table'[Name] )
VAR __date = CALCULATE ( MAX( 'Table'[number of delvieries] ), ALLSELECTED ( 'Table' ), 'Table'[Name] = __id )
CALCULATE ( Max ( 'Table'[class] ), VALUES ( 'Table'[Name ), 'Table'[Name] = __id, 'Table'[number of delvieries] = __date )

new number of delvieries =
VAR __id = MAX ( 'Table'[Name] )
VAR __date = CALCULATE ( MAX( 'Table'[number of delvieries] ), ALLSELECTED ( 'Table' ), 'Table'[Name] = __id )
CALCULATE ( Max ( 'Table'[number of delvieries] ), VALUES ( 'Table'[Name ), 'Table'[Name] = __id, 'Table'[number of delvieries] = __date )

Power Query

https://www.excelguru.ca/blog/2016/05/25/keep-the-most-recent-entry/

https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/