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...
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"
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"