Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
czuniga
Helper III
Helper III

GroupBy while retaining multiple values.

I have a dataset like the following:

a1
a1
a1
a2
b2
b3
b4
c5
d4
d4
d6
d7
e8
e8
e9
e8
e8
e8

 

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! 

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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:

29.png

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

 

Best Regards,
Community Support Team _ Eason

 

 

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

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:

29.png

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

 

Best Regards,
Community Support Team _ Eason

 

 

mahoneypat
Employee
Employee

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

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


nvprasad
Solution Sage
Solution Sage

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.