Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.