Find max of set of values in 1 data query

[Anonymous]

Hi All, please find the example reconstructed in Excel below. The real file is 600k rows long and has ~75 "items" spread over ~35 "sets".

This is my first post on the Forum, so please let me know how to improve the formatting if necessary.

Basically I have a table similar to the one on the left imported as a .csv file into Power Query and want to create the Pivot Table on the far right. 

The piece I am missing is how to create the "MaxNumber" column which determines the max "number" of each "item", marked in red.

I am not experienced at all with Power Query programming so all help and pointers would be highly appreciated!

Thanks a lot!

[slorin · Accepted solution]

Hi

let
Source = YourSource,
Max_Item = Table.Group(Source, {"Item"}, {{"Max Number", each List.Max([Number]), type number}}),
Set = Table.AddColumn(Max_Item, "Set", each Text.BeforeDelimiter([Item],"-")),
Average_Set = Table.Group(Set, {"Set"}, {{"Average Number of Set", each List.Average([Max Number]), type number}})
in
Average_Set

 Stéphane

[Anonymous]

Hi @slorin ! I couldn't get the code to work directly, but thanks for pointing me towards the Group function!

Creating a query from the sample Excel table "Table1" myself and following your steps, this M-code did the trick:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Changed_Type = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Number", type number}}),
    Max_Item = Table.Group(Changed_Type, {"Item"}, {{"MaxNumber", each List.Max([Number]), type number}}),
    Add_Set_column = Table.AddColumn(Max_Item, "Set", each Text.BeforeDelimiter([Item], "-"), type text),
    Average_Set = Table.Group(Add_Set_column, {"Set"}, {{"AverageSet", each List.Average([MaxNumber]), type number}})
in
    Average_Set

Many thanks!