Forum Discussion

Nandor's avatar
Nandor
Helper I
5 years ago
Solved

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 ...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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.

     

AlB's avatar
AlB
Community Champion
5 years ago

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's avatar
Nandor
Helper I
5 years ago

Thank you! It worked like magic.