Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StephenF
Responsive Resident
Responsive Resident

[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
HotChilli
Super User
Super User

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

View solution in original post

v-piga-msft
Resident Rockstar
Resident Rockstar

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.

View solution in original post

5 REPLIES 5
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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.

 

 

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

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

HotChilli
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.