Group By

[tomshaw83]

Hello,

I've started using Group By in the query editor, and think I have a very basic query, but one which I haven't been able to figure out just yet...

In the example table below I am looking to group by the Name, on the minimum value. Which I have done fine, what I want to do next is also display is also the colour and word that relate to the minimum value

Name	Value	Colour	Word
Gary Smith	6	Yellow	Bird
Gary Smith	5	Blue	Cat
Gary Smith	11	Yellow	Cat
Paul Jones	10	Green	Bird
Paul Jones	8	Yellow	Shoe
Paul Jones	7	Yellow	Cat

Turn the table above into the table below in the Query Editor

Name	MinimumValue	Colour	Word
Gary Smith	5	Blue	Cat
Paul Jones	7	Yellow	Cat

For now I can get the first 2 columns, and a table with 'all rows' that I don't know how to pick the 'Colour' and 'Word' values

Any helps would be appreciated

Thanks

Tom

[Anonymous · Accepted solution]

you should merge the original table with the grouped one.

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck8sqlQIzs0syVDSUTID4sjUnJz8cgUgyymzKEUpVgdNjSlIJqc0FUg5J5Zgyhsawg1BUhKQWJqj4JWfl1oMUmIAJNyLUlPzkG1BUWKBbEhwRn4qphJzDHtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, Colour = _t, Word = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Name", type text}, {"Value", Int64.Type}, {"Colour", type text}, {"Word", type text}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Name"}, {{"minVal", each List.Min([Value]), type nullable number}}),
    #"Merge di query eseguito" = Table.NestedJoin(#"Raggruppate righe", {"Name", "minVal"}, #"Modificato tipo", {"Name", "Value"}, "tab", JoinKind.Inner),
    #"Tabella tab espansa" = Table.ExpandTableColumn(#"Merge di query eseguito", "tab", {"Colour", "Word"}, {"tab.Colour", "tab.Word"})
in
    #"Tabella tab espansa"