GroupBy while retaining multiple values.

[czuniga]

I have a dataset like the following:

a	1
a	1
a	1
a	2
b	2
b	3
b	4
c	5
d	4
d	4
d	6
d	7
e	8
e	8
e	9
e	8
e	8
e	8

I'm trying to groupby the first column while retaining the highest values in the second column. In other words, for value "a" retain all of the "1" values and for value "b" retain all of the "2" values. Any help would be greatly appreciated! 

[v-easonf-msft · Accepted solution]

Hi ， @czuniga 

If  this is  the expected result you want ,please follow these steps:

1.Create a index column in query editor

2.Create calculate column as below:

Column = 
var _minindex=MINX(FILTER('Table1','Table1'[Column1]=EARLIER(Table1[Column1])),'Table1'[Index])
Return
CALCULATE(MAX('Table1'[Coulumn2]),FILTER(ALL(Table1),'Table1'[Index]=_minindex))

pbix attached 

The result will show as below:

If it doesn't meet your requirement,please  show me your expected result in excel.

Best Regards,
Community Support Team _ Eason

[mahoneypat]

Here is one way to do it in the query editor with your example data.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitXBzTICs5JQWMZwlgmYlQxkmYJZKXAxVJYZnGUOZqUCWRZYWJZ4ZYGsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1", "Column2"}, {{"AllRows", each _, type table [Column1=text, Column2=text]}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Column1"}, {{"AllRows2", each _, type table [Column1=text, Column2=text, AllRows=table]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Indexed", each Table.AddIndexColumn([AllRows2],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows2"}),
    #"Expanded Indexed" = Table.ExpandTableColumn(#"Removed Columns", "Indexed", {"Column2", "AllRows", "Index"}, {"Column2", "AllRows", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Indexed", each ([Index] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Column2"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns1", "AllRows", {"Column2"}, {"Column2"})
in
    #"Expanded AllRows"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[nvprasad]

Hi,

In the field section where you dragged the value column, select "Maximum" from the dropdown of the "Value" field.

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[czuniga]

 I was premature in marking this as the solution. It only retains a single value when selecting "Max" rather than multiple values 😞