Get result for group based on values in subgroups

[tejasp]

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-Group1	Met	Partially Met
Group1	Sub-Group1	Partially Met	Partially Met
Group1	Sub-Group1	Partially Met	Partially Met
Group2	Sub-Group2	Met	Not Met
Group2	Sub-Group2	Partially Met	Not Met
Group2	Sub-Group2	Partially Met	Not Met
Group2	Sub-Group2	Not Met	Not 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,

[Payeras_BI · Accepted solution]

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"