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.

 

column1column2column3column4
Group1Sub-Group1MetPartially Met
Group1Sub-Group1Partially MetPartially Met
Group1Sub-Group1Partially MetPartially Met
Group2Sub-Group2MetNot Met
Group2Sub-Group2Partially MetNot Met
Group2Sub-Group2Partially MetNot Met
Group2Sub-Group2Not MetNot Met

 

In above example, column4 is result column based on the values from column3. If all values of subgroup are met then group value should be met, simillary if any of the subgroup have value partially met, or not met then the value of the group should be Partially met or not met, else met. Is it possible in power query?

 

Thanks,

 

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"

1 Reply

  • Payeras_BI's avatar
    Payeras_BI
    Solution 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"