Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to group a table with a detail column ?

Hello ðŸ™‚

 

Is it possible with power query to use Table.Group with a min or max as an aggregated column and also having some other columns associated to the result of the min or max ? 

 

For example if I have : 

 

First Name           Last Name              Age                   Job

 

Marie                     Doe                       23                     Nurse

Celia                      Doe                       38                     Painter

Frederic                 Martin                   40                     Estate Agent

Thomas                 Martin                    25                    Student

Cedric                    Martin                    33                   Doctor

 

I would like to have as a result (grouping by the first name and having the minimum of the age as an aggregated column) :

 

Last Name              Age                   Job

 

 Doe                       23                     Nurse

Martin                    25                    Student

 

If it is not clear, let me know I will try to use another example ! 

 

Thanks !

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    Anonymous Here is one way:

    OriginalTable query:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sykxV0lFyyQeRRsZAwq+0qDhVKVYnWsk5NSczES5pbAEkAhIz80pSi8DSbkWpKalFmclAYaAxJZl5QIaJAZBwLS5JLElVcExPzSsBqwzJyM9NLEZWZ2QKJIJLSlNgSpxTU9CMMjYGW51ckg+0LhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t, Age = _t, Job = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", type text}, {"Last Name", type text}, {"Age", Int64.Type}, {"Job", type text}})
in
    #"Changed Type"



Grouping Table query:
let
    Source = OriginalTable,
    #"Grouped Rows" = Table.Group(Source, {"Last Name"}, {{"Age", each List.Min([Age]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Last Name", "Age"}, OriginalTable, {"Last Name", "Age"}, "OriginalTable", JoinKind.LeftOuter),
    #"Expanded OriginalTable" = Table.ExpandTableColumn(#"Merged Queries", "OriginalTable", {"Job"}, {"OriginalTable.Job"})
in
    #"Expanded OriginalTable"

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous Here is one way:

    OriginalTable query:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sykxV0lFyyQeRRsZAwq+0qDhVKVYnWsk5NSczES5pbAEkAhIz80pSi8DSbkWpKalFmclAYaAxJZl5QIaJAZBwLS5JLElVcExPzSsBqwzJyM9NLEZWZ2QKJIJLSlNgSpxTU9CMMjYGW51ckg+0LhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t, Age = _t, Job = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", type text}, {"Last Name", type text}, {"Age", Int64.Type}, {"Job", type text}})
in
    #"Changed Type"



Grouping Table query:
let
    Source = OriginalTable,
    #"Grouped Rows" = Table.Group(Source, {"Last Name"}, {{"Age", each List.Min([Age]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Last Name", "Age"}, OriginalTable, {"Last Name", "Age"}, "OriginalTable", JoinKind.LeftOuter),
    #"Expanded OriginalTable" = Table.ExpandTableColumn(#"Merged Queries", "OriginalTable", {"Job"}, {"OriginalTable.Job"})
in
    #"Expanded OriginalTable"
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Perfect, thanks ! 

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Anonymous If it solves your issue, please mark as solution so that it benefits others.

  • ngct1112's avatar
    ngct1112
    Icon for Post Patron rankPost Patron
    5 years ago

    Same solution in one Query. Anonymous 

    Please try

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sykxV0lFyyQeRRsZAwq+0qDhVKVYnWsk5NSczES5pbAEkAhIz80pSi8DSbkWpKalFmclAYaAxJZl5QIaJAZBwLS5JLElVcExPzSsBqwzJyM9NLEZWZ2QKJIJLSlNgSpxTU9CMMjYGW51ckg+0LhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t, Age = _t, Job = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Last Name"}, {{"Age", each List.Min([Age]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Age","Last Name"}, #"Changed Type", {"Age","Last Name"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Job"}, {"Grouped Rows.Job"})
in
    #"Expanded Grouped Rows"