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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Nandor
Helper I
Helper I

Group by code modified to include an if statement

Hi everybody,

 

I would need some help with a grouping based on the value from a column.

I have a column [Operation] which contains the following two options:

Group - With max

Group - With sum

 

My table looks like this:

Article nameArticle valueOperation
Article 110Group - With max
Article 120Group - With max
Article 110Group - With max
Article 215Group - With sum
Article 225Group - With sum
Article 230Group - With sum

 

After grouping it should look like this:

Article nameArticle value
Article 120
Article 270

 

I tried to modify the formula like this:

= Table.Group(#"Reordered Columns1", {"Article name"}, {{"Article value", each if [Operation] = "Group - With sum" then List.Sum([Article value]) else List.Max([Article value]), type nullable number}})

 

With this formula I get always the False version, the List.Max

The result is always:

Article nameArticle value
Article 120
Article 230

 

The if statement is not evaluating correctly.

 

Thanks for helping me out.

Nandor

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

Hi @Nandor 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwqyUzOSVUwVNJRMjQAEu5F+aUFCroK4ZklGQq5iRVKsTqoqoyIUkXYLCOQKlN0VcWluRiqjIhSZYxhI1hVLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Article name" = _t, #"Article value" = _t, Operation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Article name", type text}, {"Article value", Int64.Type}, {"Operation", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Article name"}, {{"Article value", each if List.Max([Operation]) = "Group - With max" then List.Max([Article value]) else if List.Max([Operation]) = "Group - With sum" then List.Sum([Article value]) else null}})
in
    #"Grouped Rows"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

5 REPLIES 5
AlB
Community Champion
Community Champion

@Nandor

Sure. No actual magic  🙂

When you group by Article name, [Operation] will give you , for "Article name" = Article 1,  

{"Group - With max", "Group - With max", "Group - With max"}. So, since they are repeated values, we are just interested in getting one. So List.Max([Operation]) would do but you could also do List.Min([Operation]) or 

List.Min([Operation]){0} to get the first one.

Max when applied to text will give the last one by alphabetical order

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Thank you for your time!

AlB
Community Champion
Community Champion

Hi @Nandor 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwqyUzOSVUwVNJRMjQAEu5F+aUFCroK4ZklGQq5iRVKsTqoqoyIUkXYLCOQKlN0VcWluRiqjIhSZYxhI1hVLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Article name" = _t, #"Article value" = _t, Operation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Article name", type text}, {"Article value", Int64.Type}, {"Operation", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Article name"}, {{"Article value", each if List.Max([Operation]) = "Group - With max" then List.Max([Article value]) else if List.Max([Operation]) = "Group - With sum" then List.Sum([Article value]) else null}})
in
    #"Grouped Rows"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Dear @AIB

 

Everything is working fine, I would only like to understand the logic behind the formula.

You enclosed the if condition into a List.Max function List.Max([Operation])

The values in [Operation] are text, how is this working?

You use the same max function for the other value and it is working again.

As I mentioned in my previous post, it is magic. Please help me to understand the trick.

 

Thank you,

Nandor

 

 

Thank you! It worked like magic.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.