Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !
Solved! Go to 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"
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"
Perfect, thanks !
@Anonymous If it solves your issue, please mark as solution so that it benefits others.
@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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.