How to group a table with a detail column ?

[Anonymous]

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 !

[ngct1112]

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"

[Anonymous]

Perfect, thanks ! 

[Greg_Deckler]

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

[Greg_Deckler · Accepted solution]

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