Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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. |
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!
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. |
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
11 | |
9 | |
7 | |
6 |