Group by code modified to include an if statement

[Nandor]

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 name	Article value	Operation
Article 1	10	Group - With max
Article 1	20	Group - With max
Article 1	10	Group - With max
Article 2	15	Group - With sum
Article 2	25	Group - With sum
Article 2	30	Group - With sum

After grouping it should look like this:

Article name	Article value
Article 1	20
Article 2	70

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 name	Article value
Article 1	20
Article 2	30

The if statement is not evaluating correctly.

Thanks for helping me out.

Nandor

[AlB]

@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

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.

[Nandor]

Thank you for your time!

[AlB · Accepted solution]

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"

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.

[Nandor]

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

[Nandor]

Thank you! It worked like magic.