Forum Discussion

tejasp's avatar
tejasp
Frequent Visitor
3 years ago
Solved

Get result for group based on values in subgroups

Hi Team, I am looking for a solution in power query where I will be able to get the result for main group based on the subgroup value.   column1 column2 column3 column4 Group1 Sub-Grou...
Need Help
  • Payeras_BI's avatar
    Payeras_BI
    3 years ago

    Hi tejasp ,

    One possible solution:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy0wVNJRCi5N0oVzfFNLlGJ1cMkGJBaVZCbm5FQqkKXOCFmdEbptGLLEmkKsOr/8EoiKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column1 = _t, column2 = _t, column3 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"column1", "column2"}, {{"All", each _}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Column4", each let List = [All][column3] in 
if List.Contains(List,"Not Met") then "Not Met" 
else if List.Contains(List, "Partially Met") then "Partially Met" else "Met"),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Custom", "All", {"column3"}, {"column3"})
in
    #"Expanded All"
Payeras_BI's avatar
Payeras_BI
Icon for Solution Sage rankSolution Sage
3 years ago

Hi tejasp ,

One possible solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy0wVNJRCi5N0oVzfFNLlGJ1cMkGJBaVZCbm5FQqkKXOCFmdEbptGLLEmkKsOr/8EoiKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column1 = _t, column2 = _t, column3 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"column1", "column2"}, {{"All", each _}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Column4", each let List = [All][column3] in 
if List.Contains(List,"Not Met") then "Not Met" 
else if List.Contains(List, "Partially Met") then "Partially Met" else "Met"),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Custom", "All", {"column3"}, {"column3"})
in
    #"Expanded All"