Forum Discussion
Solved
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...
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
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