Forum Discussion
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 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
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.
5 Replies
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.
Thank you! It worked like magic.
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
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.
Thank you for your time!
