Keeping max values when grouping in Power Query

[hobosapien]

In the screenshot below I have multiples of the same Genre and the counts for each artist in that Genre. How do I only keep the max value count for each artist/genre combo?

[PhilipTreacy · Accepted solution]

@hobosapien 

When you group by Genre and Artist name, select the Max aggregation in the Operation drop down

Regards

Phil

[Kedar_Pande · Accepted solution]

In Power BI, go to the "Transform Data" option to open Power Query Editor.

Go to the "Home" tab and click on "Group By".

In the Group By window:

• In the Group By section, select "Genre" and "artist_name".
• In the New column name section, name it something like Max Count.
• In the Operation dropdown, select Max and choose the Count column.

[Anonymous · Accepted solution]

Hi @hobosapien ,

Thanks for @Kedar_Pande and @PhilipTreacy reply.

@hobosapien Here is another method for your reference if you want to add a column that calculates the maximum value of each category.

= Table.AddColumn(#"Last step", "MAX", (x)=>List.Max(Table.SelectRows(#"Last step", (y)=>y[Genre]=x[Genre] )[Count]) 
)

The result of my test.

Best regards,

Mengmeng Li

[Kedar_Pande · Accepted solution]

In Power BI, go to the "Transform Data" option to open Power Query Editor.

Go to the "Home" tab and click on "Group By".

In the Group By window:

• In the Group By section, select "Genre" and "artist_name".
• In the New column name section, name it something like Max Count.
• In the Operation dropdown, select Max and choose the Count column.

[PhilipTreacy · Accepted solution]

@hobosapien 

When you group by Genre and Artist name, select the Max aggregation in the Operation drop down

Regards

Phil