Forum Discussion
GroupBy while retaining multiple values.
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!
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))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
4 Replies
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 PrasadI was premature in marking this as the solution. It only retains a single value when selecting "Max" rather than multiple values 😞
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
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))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
