cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
StephenF Member
Member

[Power query editor] How to select only records that are the max of two group by columns.

 

So basically im trying to select only the rows where each asset id has the max views  

eg how to convert:

 

Asset IDArticle Titleviews
935605Traffic Updates: Collision on M50 southbound causing delays57
935605Latest Traffic: Multi-vehicle collision on the M4130
935605Daily Traffic Updates: Heavy eastbound along the Grand Canal27
935605Latest Traffic: Rush hour busy with schools back50
935605Daily Traffic Updates: R352 closed after vehicle fire near Ennis44
1001467Nurmagomedov beats Conor McGregor but loses his head107
1001467Khabib beats Conor McGregor but loses his head23

 

to

 

Asset IDArticle Titleviews
935605Latest Traffic: Multi-vehicle collision on the M4130
1001467Nurmagomedov beats Conor McGregor but loses his head107
2 ACCEPTED SOLUTIONS

Accepted Solutions
HotChilli Senior Member
Senior Member

Re: [Power query editor] How to select only records that are the max of two group by columns.

Briefly:

Create a duplicate of your query.

On one query, choose 'group by' Asset Id, select 'Max' and views. This will give you a 2 column table.

Merge that query with the original, on both columns, choose 'inner join'.

Tidy up the result

Community Support Team
Community Support Team

Re: [Power query editor] How to select only records that are the max of two group by columns.

Hi @StephenF,

 

By my tests, the solution from HotChilli should be useful.

 

In addition, you could add another aggregation to group the Asset ID so that you could achieve your desired output more easier.

 

group by.PNG

 

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
HotChilli Senior Member
Senior Member

Re: [Power query editor] How to select only records that are the max of two group by columns.

Briefly:

Create a duplicate of your query.

On one query, choose 'group by' Asset Id, select 'Max' and views. This will give you a 2 column table.

Merge that query with the original, on both columns, choose 'inner join'.

Tidy up the result

Community Support Team
Community Support Team

Re: [Power query editor] How to select only records that are the max of two group by columns.

Hi @StephenF,

 

By my tests, the solution from HotChilli should be useful.

 

In addition, you could add another aggregation to group the Asset ID so that you could achieve your desired output more easier.

 

group by.PNG

 

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
StephenF Member
Member

Re: [Power query editor] How to select only records that are the max of two group by columns.

Yes, this doesnt work Cherry, Max on Article simple retuns the field with the highest ascii value whereas I want the article with the highest number of pageviews.

 

I couldnt even get the other approach to work.

 

 

HotChilli Senior Member
Senior Member

Re: [Power query editor] How to select only records that are the max of two group by columns.

Stephen, which part of the solution i gave did you have trouble with?

StephenF Member
Member

Re: [Power query editor] How to select only records that are the max of two group by columns.

Hi,

 

I found this solved my issue most simply:

 

A grouped index where you filter on the index=1

 

https://www.youtube.com/watch?v=-3KFZaYImEY

 

Thanks for your time.

 

Stephen