Forum Discussion

tomshaw83's avatar
tomshaw83
Helper I
5 years ago
Solved

Group By

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

 

NameValueColourWord
Gary Smith6Yellow Bird
Gary Smith5BlueCat
Gary Smith11YellowCat
Paul Jones10GreenBird
Paul Jones8YellowShoe
Paul Jones7YellowCat

 

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

NameMinimumValueColourWord
Gary Smith5BlueCat
Paul Jones7YellowCat

 

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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"

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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"